Searching for alphabetical characters

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 2008 Forums

Transact-SQL (2008)

Searching for alphabetical characters

Author

Topic

roger00713
Starting Member

11 Posts

Posted - 2011-02-07 : 22:35:24

Hi.

I am needing to convert some fields from datatype varchar to smallint.

These fields are used to store a test result from 1 - 100. (Had i created the table i would have used smallint in the first place!).

When running the conversion i am getting an error which is allerting me to the fact that there must be an alphabetical character hiding somewhere and the conversion fails.

Is there some way to seach for these characters without haveing to go through 20 fields each with 20,000 rows in the manually?

Any help with be a big time saver and will be much appreciated.

Thanks.

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2011-02-07 : 23:30:32

Select * from tableName
Where ColumnName like '%[a-zA-Z]%'

Will display all the rows having characters A-Z.

roger00713
Starting Member

11 Posts

Posted - 2011-02-07 : 23:54:48

Massive help mate. Thanks.

Using the query i found 3 records that had issues although i still cant convert and am getting the same error.

In the case that it may be a special character is there a way to check for these?

Thanks.

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2011-02-08 : 00:17:16

come up with an example along with the error message. As it will help us/me to point out the problematic area rather guessing it for you ...

Yes you can search any character you want .. put all special characters just before the a-z inside brackets. Hope you will get what you want!

Cheers
MIK

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-02-08 : 01:49:22

Select * from tableName
Where ColumnName like '%[^0-9]%'

Madhivanan

Failing to plan is Planning to fail

sundar_venkat
Starting Member

2 Posts

Posted - 2011-02-10 : 23:48:30

update t1 set c1=...
where upper(c1) <> lower(c1)

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-02-11 : 03:19:06

quote:
Originally posted by sundar_venkat

update t1 set c1=...
where upper(c1) <> lower(c1)

Did you read the question properly?

Madhivanan

Failing to plan is Planning to fail

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2011-02-11 : 04:33:35

quote:
Originally posted by sundar_venkat
...
where upper(c1) <> lower(c1)

This would only work if the database collation is case sensitive:

select case when upper('12a' COLLATE Latin1_General_CI_AS_KS_WS) <> LOWER('12a' COLLATE Latin1_General_CI_AS_KS_WS) then 1 else 0 end
select case when upper('12' COLLATE Latin1_General_CI_AS_KS_WS) <> LOWER('12' COLLATE Latin1_General_CI_AS_KS_WS) then 1 else 0 end

select case when upper('12a' COLLATE Latin1_General_CS_AS_KS_WS) <> LOWER('12a' COLLATE Latin1_General_CS_AS_KS_WS) then 1 else 0 end
select case when upper('12' COLLATE Latin1_General_CS_AS_KS_WS) <> LOWER('12' COLLATE Latin1_General_CS_AS_KS_WS) then 1 else 0 end

- Lumbago
My blog-> http://thefirstsql.com/2011/02/07/regular-expressions-advanced-string-matching-and-new-split-function-sql-server-2008-r2/

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2011-02-11 : 04:46:47

and it wouldn't catch characters such as '@:"£$%^&*()/.........'

Madhi's suggestion is probably exactly what you need:

Select * from tableName
Where ColumnName like '%[^0-9]%'

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources