Detecting Specific Characters in a Varchar Field

  • Hi,

    I would like to return the results from Varchar field where there are spaces in the field.

    Is there a way to limit results to where there are empty spaces or characters such as '_ , *, .)

    Any links to string manipulation would also be appreciated.

    Thanks

  • You can get the location of the characters you are looking for by using patindex

    e.g.

    declare @a varchar(10)

    set @a = 'abcd a_b'

    select patindex('%[ _]%', @a)

    If you need to remove things, you can use replace, substring, ...

    You can read on string manipulation functions that are available in T-SQL on http://msdn.microsoft.com/en-us/library/ms181984.aspx

    However, if you need something more complicated, you may way to look at CLR functions.

    Regards,

    Andras


    Andras Belokosztolszki, MCPD, PhD
    GoldenGate Software

  • Thank you very much for this, this is exactly what i'm trying to accomplish.

    :hehe:

  • this is really great

  • declare @a varchar(10)

    set @a = 'abcd a_b'

    select patindex('%[ _]%', @a)

    An alternative would be to use

    declare @a varchar(10)

    set @a = 'abcd a_b'

    select charindex('_', @a)

    I don't see a difference between the 2 functions. I posted this code as this is what I would have gone for first. Maybe someone can provide more info?

  • The patindex is checking for two chars _ and space

    charindex('_', @a) will return position of first _ char

    patindex('%[ _]%', @a) will return position of EITHER _ or space

    charindex('_', @a) and patindex('%[_]%', @a) would give the same result (ie no space)

    if checking for presence of _ and space then

    CHARINDEX('_', @a) > 0 AND CHARINDEX(' ', @a) > 0

    would be the same as

    PATINDEX('%[ _]%', @a) > 0

    and it would get worse if you wanted to check for the letters a to f as you would have to OR 6 CHARINDEX

    or use PATINDEX('%[a-f]%', @a)

    Far away is close at hand in the images of elsewhere.
    Anon.

  • Ahem.....

    Just did some investigating....:blink:

    declare @a varchar(10)

    set @a = 'abcd a_b'

    select charindex('cd', @a) -- matches a pattern - 'cd' is found in @a

    select charindex('ce', @a) -- matches a pattern - 'ce' not found in @a

    select patindex('%[zce]%', @a) -- treats each of the letters as seperate and tries to find any

    --1 of them in @a

    select patindex('%[ce]%', @a) -- same outcome as above

    Still let me know if there is any other difference between the 2. It may help someone else reading this post.

  • Thanks David.

    Didnt see your reply there as I was typing out mine. The guys here on SQL ServerCentral reply really quick:smooooth:

  • You're welcome 🙂

    Far away is close at hand in the images of elsewhere.
    Anon.

Viewing 9 posts - 1 through 8 (of 8 total)

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