Searching for Full Stops - 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

General SQL Server Forums

New to SQL Server Programming

Searching for Full Stops

Author

Topic

Kyle Doouss
Yak Posting Veteran

64 Posts

Posted - 2011-11-08 : 03:38:33

I am converting a varchar field to float on any fields not containing a character. I am having an issue in trying to identify whether a field has a full stop in.

I am current using the following

case when
OD_DETAIL like '%a%'
or OD_DETAIL like '%b%'
or OD_DETAIL like '%c%'
or OD_DETAIL like '%d%'
or OD_DETAIL like '%e%'
or OD_DETAIL like '%f%'
or OD_DETAIL like '%g%'
or OD_DETAIL like '%h%'
or OD_DETAIL like '%i%'
or OD_DETAIL like '%j%'
or OD_DETAIL like '%k%'
or OD_DETAIL like '%l%'
or OD_DETAIL like '%m%'
or OD_DETAIL like '%n%'
or OD_DETAIL like '%o%'
or OD_DETAIL like '%p%'
or OD_DETAIL like '%q%'
or OD_DETAIL like '%r%'
or OD_DETAIL like '%s%'
or OD_DETAIL like '%t%'
or OD_DETAIL like '%u%'
or OD_DETAIL like '%v%'
or OD_DETAIL like '%w%'
or OD_DETAIL like '%x%'
or OD_DETAIL like '%w%'
or OD_DETAIL like '%z%'
or OD_DETAIL like '%-%'
or OD_DETAIL like '%,%'
or OD_DETAIL like '%.%'or OD_DETAIL like '%?%'
or OD_DETAIL like '%:%'
or OD_DETAIL like '%;%'
or OD_DETAIL like '%)%'
or OD_DETAIL like '%(%'
or OD_DETAIL like '% %'
or OD_DETAIL like '%"%'
or OD_DETAIL like '%"%'
or OD_DETAIL like '' then null else
cast( OD_DETAIL as float)end as ORIGINAL_ORDER_NUMBER

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-08 : 03:43:26

isnt this enough instead of all above conditions?

yourfield like '%[^0-9]%'

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/