Need to find the values other than a-z in an account name.

[colezpapa]

I am trying to build a query that checks an account name field for any values other than 'a-z' and '0-9'
I am not sure how to do this, i tried the transform but got back really wierd results.

 

[MarcLodge]

Colezpapa,
Not quite sure here what you wish to see returned back from the query. Do you want it to return just the unusual characters or do you just want to know if characters other than a-z 0-9 exist? If they don't exist, what do you want the query to return?

 

[colezpapa]

Hi

If there are unusual characters, I would want the unusual characters.

For example...

Account Character
First Natio&nal Express.... &

Something like that....

 

[blom0344]

I'd say this is something for a stored procedure..

Ties Blom

 

[MarcLodge]

I agree with Ties on this. I'm not sure if it could be done, but it would be much better suited to a stored procedure/programming language.

Marc

 

[colezpapa]

Yep....I am doing it in VBA....
I was hoping I could do it in SQL...

thx anyway

 

[sathyarams]

select tabname from syscat.tables where NOT(
locate(' ',tabname )=0 and
length(strip(translate(tabname,'','ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789')))=0 ) "

This list all tables that hav non-alphanumeric in their names .. "_" as an example.

The locate is used to ensure there are no blank spaces in the string.

Hope this gives an idea of how to tackle your requirement.

Cheers
Sathyaram

For db2 resoruces visit

http://www.db2click.com

More DB2 questions answered at

http://www.dbforums.com/f8

&

http://www.idug.org/user/UserLogin.asp

 

[blom0344]

Cool, remember now seeing something similar before..

Ties Blom