Find non-alphanumeric characters in a string

[micang]

SQL 2000\Query Analyser

Hi All,

Is there a way of finding non-alphanumeric ('-', '*' etc and a space ' ') characters in a varchar (20) string?

The characters can be in any position in the string.

Any help appreciated.

Michael

 

[gmmastros]

You can find the first non alpha-numeric character in a string, like this...

[COLOR=blue]Declare[/color] @Temp [COLOR=blue]VarChar[/color](20)

[COLOR=blue]Set[/color] @Temp = [COLOR=red]'19238asdkj#sokfjsd9f'[/color]

[COLOR=blue]Select[/color] [COLOR=#FF00FF]PatIndex[/color]([COLOR=red]'%[^0-9A-Z]%'[/color], @Temp)

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[micang]

Thanks George. Excuse my ignorance, but I have never used declare before.

How would I apply this to a table and a specific column?

Thanks in advance.

Michael

 

[gmmastros]

You don't I declared a variable, set some data, and then tested some code. To use a column in a table...

Select PatIndex('%[^0-9A-Z]%', [!]Column[/!])
From   [!]Table[/!]

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[micang]

blush...

Thanks George, I see it shows a 0 where they are all alpha-numeric otherwise the character position/number of the first non-alpha-numeric.

This is a great help and start.

Many thanks.

Michael