Post reply

Simple PATINDEX and Escape codes

  • September 8, 2004 at 8:55 am

    #60189

    HI

    Trying to search for a particular set of characters within a string.

    The problems is, some of the characters I'm looking for appear to be escape codes in SQL Server.

    Essentially I want to look for ANYTHING that may be a delimeter.

    Something like -,|`~ etcetera...

    So, this:

    SELECT PATINDEX('%[`~!@#$&*()+={}|\:;%"<>,.]%', 'A.A')

    Returns 2 which is correct.

    I've found the position of the period(.) character.

    However, if I start adding any of the following characters - the results start varying since they are escape codes.

    %^-_[]'

    How can I also add the listed characters (%^-_[]') to my list or characters to search for ?

    Thanks in advance - B

  • September 8, 2004 at 10:15 am

    #522152

    Double them up. ie. the percent sign '%' is a wildcard to sql. To look for a percent sign specifically you would place it in the literal twice - patindex('%%')

    Steve

  • September 8, 2004 at 10:19 am

    #522153

    Thanks Steve.

    In the meantime I'm simply doing the negative - or looking for character NOT in the list I'm looking for.

     

    This: SELECT PATINDEX('%[^a-z0-9]%', 'AbC12|3ABC')

    Returns 6 which is correct for me.

    I'm only looking for alphas and numbers as being valid.

    All others are considered delimeters.

    Thanks again - B

  • September 8, 2004 at 10:27 am

    #522159

    Watch those ranges....

    create table #search(somechar char(1))

    go

    declare @i int

    set @i = 1

    while @i < = 256

    begin

    insert #search

    select char(@i)

    where char(@i) not like '[abcdefghijklmnopqrstuvwxyz0123456789]'

    set @i = @i + 1

    end

    go

    SELECT *

    from #search

    where PATINDEX('%[^a-z0-9]%', somechar) = 0

    go

    --
    Adam Machanic
    whoisactive

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

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