Find distinct special characters in column

Question

Find distinct special characters in column

shamshad.ali

Hall of Fame

Points: 3850
More actions
August 8, 2008 at 11:35 pm

#190893

I have a Table in which there is a Column Named as NTLoginID, we keep users NT Login in this column. All special characters are acceptable in NTLogin except few. we have almost 400K users NTLogin IDs in this table.
I would like to know from database and report developers that application should check and take care of special characters that does not break application. like apostrophe, double quote all kind braces. etc.
Is there any way to find only special characters ?
NTLoginIDs
Nancy'ColnN
JhonVil[2]
and so many others.
Please help.
Shamshad Ali.

Viewing 15 posts - 1 through 15 (of 16 total)

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

Atif-ullah Sheikh SSChampion Points: 12495 More actions · Answer 1

Are you having problem in Insertion / updation or in querying the records...?

I think inall cases, you should check the special characters (that can cause error in the query if not handeled) in the application.

For example, if you have name with value Nancy'Collin, and you have to query the record with where condition on name column, the query should go like

Select * from YourTabnle where name = 'Nancy''Collin'

Look for the two single quotes in 'Nancy''Collin'.

This can be easily done at application while passing the parameter to the database.

Same should be done in Insertion and updation...

I think it would resolve your problem.

Atif Sheikh

----------------------------------------------------------------------------------------------------------------------------------------------------------------------

Sometimes, winning is not an issue but trying.
You can check my BLOG [font="Arial Black"]here[/font][/url][/right]

Atif-ullah Sheikh SSChampion Points: 12495 More actions · Answer 2

You can find the special characters at the database end by using ASCI codes. this will require string parsing. can be done using tally tables or looping through each cahracter in the NTLogin column.

Read the following article for parsing the strings in both ways, tally table and Looping.

http://www.sqlservercentral.com/articles/TSQL/62867/">

http://www.sqlservercentral.com/articles/TSQL/62867/

Atif Sheikh

----------------------------------------------------------------------------------------------------------------------------------------------------------------------

Sometimes, winning is not an issue but trying.
You can check my BLOG [font="Arial Black"]here[/font][/url][/right]

shamshad.ali Hall of Fame Points: 3850 More actions · Answer 3

Thanks for your reply, but this really does not help me. also the link u sent is not accessible. Plz. verify.

The problem is I have to "Find DISTINCT SPECIAL characters in column [NTLogin]"

Data is already there. when users connect application, application get their NTLogin without any problem and save it in database. after that we have problem comes in SELECT only and application breaks. we have this application from last 4 years running. I have just joined and identified this problem. "users having special character in their NTLogin are facing problem running application". so i wanted to find out all those users that have such special charaters and it should be fixed at code level so that application works with such users. there are almost 400K of users in table.

I have to only and only find DISTINCT SPECIAL characters found in this NTLogin column. I know i can find it by using some string functions and cursors [last option] but i want if some one has already done this job/has any quick solution, plz share. I will save my time.

Shamshad Ali.

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 4

SELECT * FROM Users

WHERE UserName LIKE '%[^a-z0-9]%'

N 56°04'39.16"
E 12°55'05.25"

RBarryYoung SSC Guru Points: 143329 More actions · Answer 5

shamshad.ali (8/9/2008)
The problem is I have to "Find DISTINCT SPECIAL characters in column [NTLogin]"

The problem is that "DISTINCT SPECIAL" does not really mean anything. What you call special is likely to be very different from what anyone else would call special. and "Distinct" doesn't really add anything to that.

What we need for you to do is to actually list the special characters that you want to check for.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 6

shamshad.ali (8/9/2008)
The problem is I have to "Find DISTINCT SPECIAL characters in column [NTLogin]"
Shamshad Ali.

With a minor modification, Peso's solution will do it...

SELECT * FROM Users

WHERE UserName LIKE '%[yourdistinctlistofcharacters]%'

Change the "yourdistinctlistofcharacters" in the above to the list of characters you wish to detect.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 7

ps. If your yourdistinctlistofcharacters is supposed to contain a single quote, you'll need to include it twice. If yourdistinctlistofcharacters is supposed to contain brackets, then you'll need to use an ESCAPE character... lookup LIKE in Books Online for more information on how to use such an escape character.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Sergiy SSC Guru Points: 110208 More actions · Answer 8

If application fails because of special characters in logins then it's programmers who wrote it must be found and eliminated, not the characters.

Those failures mean that application is not protected against SQL injections and it may be hacked in the easiest way.

Don't fight characters, fix the code.

_____________
Code for TallyGenerator

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 9

Heh... spot on, actually. Sergiy is absolutely correct.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

shamshad.ali Hall of Fame Points: 3850 More actions · Answer 10

the problem exists with NTLogin, that accept all characters except few. That is what i wanted to tell programmer that he must care of those all and the queries must not fail from code/application. for Checking existing records I wanted to query NTLogin Column that just tell me list of those characters.

Everything EXCEPT These:

•Slash (/)

•Backslash (\)

•Left Square Bracket ( [ )

•Right Square Bracket ( ] )

•Colon ( : )

•Semicolon ( ; )

•Vertical Bar ( | )

•Equal Sign ( = )

•Comma ( , )

•Plus Sign ( + )

•Asterisk ( * )

•Question Mark ( ? )

•Less-Than Sign ( < )

•Greater-Than Sign ( > )

•Ampersand ( @ )

•Double Quotation Mark ( " )

Everything except above are valid for Active directory logon name.

I don't understand which list of characters i supposed to tell programmer that he must care of all characters excluding above. because these characters never accepted in NTLogin.

Shamshad Ali.

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 11

See my first post.

It finds all NTLogins that have characters other than a-z and 0-9.

N 56°04'39.16"
E 12°55'05.25"

shamshad.ali Hall of Fame Points: 3850 More actions · Answer 12

I need to list them down. if there is a way. also how should i exclude space" " and dot"." from [a-z0-9]

Thanks all for help...

Shamshad Ali.

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 13

SELECT * FROM Users

WHERE UserName LIKE '%[^a-z0-9 .]%'

N 56°04'39.16"
E 12°55'05.25"

RBarryYoung SSC Guru Points: 143329 More actions · Answer 14

Maybe it would be easier for you to list all of the characters that are acceptable?

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]