Remove Special Characters

Anyone got easy ways to make sure a text is only numbers and letters?

If I use substitute I either need to know all characters I don’t want, or I have to put in 36 nested Substitutes together.

True!

You could maybe use EXTRACT for numbers and text as some sort of validity check, maybe?

Is the text fixed width or variable width?

Currently it takes a variable width.

It’s very difficult to find all characters that you don’t want. Because there are many :slight_smile:

You could also do this:

COUNT(
LIST(
MID([YourTextColumn],1,1),
MID([YourTextColumn],2,1),
MID([YourTextColumn],3,1),
MID([YourTextColumn],4,1),
MID([YourTextColumn],5,1),
MID([YourTextColumn],6,1),
MID([YourTextColumn],7,1),
MID([YourTextColumn],8,1),
MID([YourTextColumn],9,1),
MID([YourTextColumn],10,1)
)
-LIST(
"A","B","C","D","E","F","G","H","I","J","K","L","M","N","O","P","Q","R","S","T","U","V","W","X","Y","Z",
"a","b","c","d","e","f","g","h","i","j","k","l","m","n","o","p","q","r","s","t","u","v","w","x","y","z",
"0","1","2","3","4","5","6","7","8","9"
)
)

If this is greater than 1, then there is a special character.
This is working for a text length up to 10. But you can expand it.

4 Likes

Whats the performance of such a formula?

@Steve what do you think about performance? Will my expression be faster than a huge Substitute expression?

1 Like

I wouldn’t think it would be bad since it’s just a list difference and a count with some mid.

It depends on a variety of factors, including the length of the text values being processed, the number of values being processed, and the context in which the processing is happening (virtual column, valid_if, initial value, etc.).

My concern with you suggestion isn’t performance, but that it can’t handle values of an arbitrary length, so its applicability is limited.

2 Likes

Would this only work for length 10 or would it work for anything less than length 10?

It’ll work with less than 10, but it’ll add list elements for the missing characters. To correct for that, add "" to the subtracted LIST().