String Cleaning - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

String Cleaning

Author

Topic

iancuct
Yak Posting Veteran

73 Posts

Posted - 2005-09-21 : 12:13:47

I have some data that is dirty, is there a function that can be apllied for each character in a string to determine if its is in between lets say 0-9?

I am trying to cast some varchar to float and i get conversion errors, even though when i look at the varchar data in the table , it is in fact a number like 15, and no spaces after it. however it does have something after it but i can't see it.

the only way i can see it is if i convert to binary and look at it comparing it to the converstion to binary of '15'
so i know there is a difference.

THerefore i was thinking to just read char by char and verify if it falls between 0-9

does anyone know how to do that?

iancuct
Yak Posting Veteran

73 Posts

Posted - 2005-09-21 : 12:41:38

ok i found my own answer :)

http://www.nigelrivett.net/SQLTsql/RemoveNonNumericCharacters.html

Kristen
Test

22859 Posts

Posted - 2005-09-21 : 15:19:54

In case it helps:


SELECT MyColumn, *
FROM   MyTable
WHERE  MyColumn LIKE '%[^0-9]%'

will find all rows where MyColumn contains on-numeric digits, whereas


WHERE  MyColumn NOT LIKE '%[^0-9]%'

will find only rows that contain numeric digits only

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources