Post reply

how to find ASCII characters in a table?

  • July 29, 2011 at 9:20 am

    #242232

    Can any one tell me the sql query to find the ASCII characters (0 to 127) from multiple columns in a table.

    Thanks.

  • July 29, 2011 at 9:24 am

    #1360872

    failrly easy to generate your own table of ascii chars, but i don't understand what you meant by from mulitple columns;

    can you explain what you were after?

    here's one example:

    with Tally (N)

    AS

    ( select top 127 row_number() over (Order By Name) from sys.columns

    )

    select N-1 as CHARVALUE,CHAR(N-1) as ASCIICHAR

    from Tally

    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!

  • July 29, 2011 at 9:37 am

    #1360882

    I have a table in which a couple of columns (varchar) is having ASCii characters so how do i find them using a sql query?

    Thanks.

  • July 29, 2011 at 9:43 am

    #1360888

    ranuganti (7/29/2011)

    I have a table in which a couple of columns (varchar) is having ASCii characters so how do i find them using a sql query?

    Thanks.

    ALL characters in a varchar column are ASCII characters. Which characters are you looking for? Just "control" characters such as TABs, CARRIAGE RETURNs, LINEFEEDs, etc?

    --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)

  • July 29, 2011 at 9:46 am

    #1360890

    Yes

  • July 29, 2011 at 9:46 am

    #1360891

    ranuganti (7/29/2011)

    I have a table in which a couple of columns (varchar) is having ASCii characters so how do i find them using a sql query?

    Thanks.

    you are still not clear. if you have two rows of data in the varchar table you are talking about, say that had the values "MIAMI" and "DALLAS", what would be the expected results?

    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!

  • July 29, 2011 at 9:47 am

    #1360892

    Yes, am looking for the special charaacters in the column other than numbers and the alphabets.

    Thanks.

  • July 29, 2011 at 11:05 am

    #1360950

    Is this what you desire to do?

    CREATE TABLE #T(Id INT,SomeText VARCHAR(300))

    INSERT INTO #T

    SELECT 9,'This is 1Tab' UNION ALL

    SELECT 13, 'This is a

    line feed' UNION ALL

    SELECT 1000,'This has a taband a line

    feed' --edited ,, us a tab, carriage return and a line feed

    ----single tab character in between % in first LIKE and a carriage return and Line feed in second LIKE

    SELECT ID FROM #T WHERE SomeText LIKE '%%' OR SomeText LIKE '%

    %'

    Displaying the results as text using SSMS

    Id SomeText

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

    9 This is 1Tab

    13 This is a

    line feed

    1000 This has a taband a line

    feed

    Edited to correct tab, carriage return and line feed useage

    If everything seems to be going well, you have obviously overlooked something.

    Ron

    Please help us, help you -before posting a question please read[/url]
    Before posting a performance problem please read[/url]

  • July 29, 2011 at 11:22 am

    #1360970

    I have this type of ASCii characters in the column how do i find all in a single query other than alphanumarics.

    Thanks,

    (space)

    !

    "

    #

    $

    %

    &

    '

    (

    )

    *

    +

    ,

    -

    .

    /

    :

    ;

    <

    =

    >

    ?

  • July 29, 2011 at 11:29 am

    #1360976

    here's one way:

    select * from YOURTABLE where PATINDEX('%[ !"#$%&''()*+,-./:;<=>?]%',YOURCOLUMN) > 0

    --or

    select * from YOURTABLE where YOURCOLUMN LIKE '%[ !"#$%&''()*+,-./:;<=>?]%'

    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!

  • July 29, 2011 at 11:43 am

    #1360992

    Hey Anuganti,

    See if I understand you correctly the belwo would fit your requirement

    CREATE FUNCTION dbo.GetAsciiValue

    (

    @STR varchar(100)

    )

    RETURNS varchar(1000)

    AS

    BEGIN

    DECLARE @res varchar(1000)

    SELECT @res=COALESCE(@res,'')+CAST(ASCII(SUBSTRING(@str,number,1)) AS varchar(5))

    FROM master..spt_values

    WHERE type='p'

    AND number BETWEEN 1 AND LEN(@str)

    RETURN @res

    END

    then use it like

    select name,number,dbo.GetAsciiValue(number) AS AsciiEqv

    from yourtable

    [font="Tahoma"]
    --SQLFRNDZ[/url]
    [/font]

  • July 29, 2011 at 6:08 pm

    #1361177

    ranuganti (7/29/2011)

    I have this type of ASCii characters in the column how do i find all in a single query other than [font="Arial Black"]alphanumarics[/font].

    Let's peel one potato at a time... 🙂

    Based on what you said and what emphasized in the quote above, are you asking to be able to find data that has anything other than A to Z, a to z, and 0 to 9 in it?

    --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)

  • July 29, 2011 at 6:09 pm

    #1361178

    Heh... also... why on Earth does it have to be a "single query"? You doing something for a contest or what? 😉

    --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)

  • July 30, 2011 at 8:37 am

    #1361226

    If you need to know which characters have been found / or if found removed from the input string try this:

    DECLARE @Temp VARCHAR(30)

    DECLARE @Found VARCHAR(30)

    SET @Found =''

    SET @Temp = 'A<>B=1,! \|-*()%$#@!'

    SELECT @Temp AS 'Original input'

    BEGIN

    WHILE PATINDEX('%[^a-z^0-9]%', @Temp) > 0

    BEGIN

    SET @Found = @Found + (SELECT SUBSTRING(@Temp,PATINDEX('%[^a-z^0-9]%', @Temp),1))

    SET @Temp = STUFF(@Temp, PATINDEX('%[^a-z^0-9]%', @Temp), 1, '')

    END

    SELECT @Found AS 'Found'

    SELECT @Temp AS 'With characters removed'

    END

    Results:

    Found

    <>=,! \|-*()%$#@!

    With characters removed

    AB1

    Note that the above will NOT find or remove the '^' character.

    If everything seems to be going well, you have obviously overlooked something.

    Ron

    Please help us, help you -before posting a question please read[/url]
    Before posting a performance problem please read[/url]

  • November 1, 2012 at 7:07 am

    #1554987

    I have character or symbol in my column like "?". Please help me to find out the rows which are all having this("?") character.

    My column value: Digital ? Packet Data

    I have tried the following query :

    select * from tbl_example where PATINDEX('%[?]%',field_name) > 0

    I got results with "?" character. Please help me to find "?" character in the fields.

    Thanks!!!

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

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