Anyone got easy ways to make sure a text is only numbers and letters?
Solved! Go to Solution.
Itโs very difficult to find all characters that you donโt want. Because there are many
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.
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
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.
Whats the performance of such a formula?
@Steve what do you think about performance? Will my expression be faster than a huge Substitute expression?
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.
I wouldnโt think it would be bad since itโs just a list difference and a count with some mid.
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().
User | Count |
---|---|
36 | |
33 | |
27 | |
23 | |
18 |