To validate the column with only alphabets

Question

Post reply

To validate the column with only alphabets

rockybhai

SSCommitted

Points: 1574
More actions
June 7, 2011 at 3:19 am

#241821

To find the erroneous data in a column level, no special characters are allowed as well as no numbers are allowed (i.e) only characters A-Z or a-z are allowed in this particular column.
I tried this query but not sure whether it is correct. Please correct the query
SELECT COUNT(*) VAL2 FROM
(
SELECT COLUMN_NAME FROM TABLE_NAME WHERE (COLUMN_NAME not like '%[abcdefghijklmnopqrstuvwxyz]%'
collate Latin1_General_CS_AS AND
COLUMN_NAME not like '%[ABCDEFGHIJKLMNOPQRSTUVWXYZ]%' collate Latin1_General_CS_AS)
)B
Adding more to the question. The query should return the rows that have Data like this
Name[COLUMN_NAME]
John78
Abraham15
William_*
Phi*&()llips
D76an
Chris76_^&topher
[/center]

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Sriram.RM Hall of Fame Points: 3368 More actions · Answer 1

your query would not find if it contains special charecters.... try this..

SELECT COUNT(*) VAL2 FROM

(

SELECT COLUMN_NAME FROM TABLE_NAME WHERE (C1 NOT LIKE '%[^A-Z]%'

COLLATE LATIN1_GENERAL_CS_AS OR

COLUMN_NAME NOT LIKE '%[^A-Z]%' COLLATE LATIN1_GENERAL_CS_AS)

)B

also, in future please give the DDL statement along with sample data so that it would be easy for us to take a look at it..

[font="Times New Roman"]For better, quicker answers on T-SQL questions, click on the following...

http://www.sqlservercentral.com/articles/Best+Practices/61537/%5B/font%5D

[font="Times New Roman"]For better assistance in answering your questions
Click Here[/url][/font]

Lowell SSC Guru Points: 323518 More actions · Answer 2

Sriram.RM (6/7/2011)

your query would not find if it contains special charecters.... try this..

SELECT COUNT(*) VAL2 FROM

(

SELECT COLUMN_NAME FROM TABLE_NAME WHERE (C1 NOT LIKE '%[^A-Z]%'

COLLATE LATIN1_GENERAL_CS_AS OR

COLUMN_NAME NOT LIKE '%[^A-Z]%' COLLATE LATIN1_GENERAL_CS_AS)

)B

also, in future please give the DDL statement along with sample data so that it would be easy for us to take a look at it..

[font="Times New Roman"]For better, quicker answers on T-SQL questions, click on the following...

http://www.sqlservercentral.com/articles/Best+Practices/61537/%5B/font%5D%5B/quote%5D

I think what you posted only finds items that EVERY char in the string meets the A-z no number no char criteria...i thought he wanted to find the offending data?

to find columns where any numeric or special chars exists at all, it'd be this in the inner query, right?

SELECT COLUMN_NAME FROM TABLE_NAME WHERE (C1 LIKE '%[^A-Z]%'

COLLATE LATIN1_GENERAL_CS_AS OR

COLUMN_NAME NOT LIKE '%[^A-Z]%' COLLATE LATIN1_GENERAL_CS_AS)

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Sriram.RM Hall of Fame Points: 3368 More actions · Answer 3

Not sure what he actually wanted.:doze:.. i just modified the query what he had given... :hehe:

[font="Times New Roman"]For better assistance in answering your questions
Click Here[/url][/font]

rockybhai SSCommitted Points: 1574 More actions · Answer 4

Thanks for all your replies but I was looking for the code that would find out all the rows that have data other than characters (this includes numbers and special characters). Please help asap. Thanks in advance.....