Post reply

Find distinct special characters in column

  • 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.

  • August 9, 2008 at 1:43 am

    #855630

    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]

  • August 9, 2008 at 1:50 am

    #855633

    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://qa.sqlservercentral.com/articles/TSQL/62867/">

    http://qa.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]

  • August 9, 2008 at 3:17 am

    #855646

    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.

  • August 9, 2008 at 5:06 am

    #855663

    SELECT * FROM Users

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

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

  • August 9, 2008 at 9:36 am

    #855689

    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]

  • August 10, 2008 at 9:05 am

    #855754

    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)
    Intro to Tally Tables and Functions

  • August 10, 2008 at 9:07 am

    #855756

    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)
    Intro to Tally Tables and Functions

  • August 10, 2008 at 3:32 pm

    #855806

    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

  • August 10, 2008 at 3:51 pm

    #855807

    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)
    Intro to Tally Tables and Functions

  • August 10, 2008 at 11:02 pm

    #855878

    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.

  • August 11, 2008 at 12:32 am

    #855889

    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"

  • August 11, 2008 at 3:12 am

    #855939

    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.

  • August 11, 2008 at 4:07 am

    #855951

    SELECT * FROM Users

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

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

  • August 11, 2008 at 6:48 am

    #856040

    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]

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

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