Special Characters

  • jim.rasmussen

    SSC Eights!

    Points: 939

    I have a filed of varchar the holds names of companies. Like 'A & B Cleaners'. What I what to do is to select this.. 'ABClea'. How can I get this done?

    Thanks

    Jim

  • Jeff Moden

    SSC Guru

    Points: 994261

    jim.rasmussen (4/27/2009)


    I have a filed of varchar the holds names of companies. Like 'A & B Cleaners'. What I what to do is to select this.. 'ABClea'. How can I get this done?

    Thanks

    Jim

    Wow... that does go back a bit...

    Does your version of SQL Server have the STUFF function available?

    --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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

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

  • jim.rasmussen

    SSC Eights!

    Points: 939

    Yes it does. I have researched this, but can't seem to make it work correctly. Any thoughts?

    Thanks

    Jim

  • Jeff Moden

    SSC Guru

    Points: 994261

    jim.rasmussen (4/28/2009)


    Yes it does. I have researched this, but can't seem to make it work correctly. Any thoughts?

    Thanks

    Jim

    Sorry, Jim... I had almost 900 emails in my inbox since I posted this... I'll be back. Thanks for the info.

    --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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

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

  • Jeff Moden

    SSC Guru

    Points: 994261

    Ok, here we go... Assuming that you have the company name in a variable and the fact that you're using a version of T-SQL that cannot use functions, here's how I'd do it...

    [font="Courier New"]

    --===== Company name is in a variable

    DECLARE @CompanyName VARCHAR(256)

     SELECT @CompanyName 'A & B Cleaners' 

      PRINT @CompanyName --Just for verification... you can remove this line

    --===== Using a "Tally" table as a loop driver, remove all characters that

         -- are NOT in the in range of A to Z (upper or lower case)

     SELECT @CompanyName STUFF(@CompanyName,PATINDEX('%[^A-Z]%',@CompanyName),1,'')

       FROM dbo.Tally t

      WHERE t.N <= LEN(@CompanyName)

        AND SUBSTRING(@CompanyName,t.N,1) LIKE '[^A-Z]'

    --===== Grab just the left six characters of what remains.

     SELECT @CompanyName LEFT(@CompanyName,6)

    --===== Display the result (just for verification... you can remove this line)

      PRINT @CompanyName[/font]

    If you don't have a Tally table, please see the article at the following URL for how to build one, what it is, and how it works. It's a very useful tool that can frequently be used to replace loops.

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

    If you need to do this to a whole table column, please post back.

    --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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

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

  • Jeff Moden

    SSC Guru

    Points: 994261

    Any feedback on this, Jim?

    --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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

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

  • Stephen.Richardson

    SSChasing Mays

    Points: 648

    Not totaly clear about if you wanted to remove all spaces or just leading?? If you are tataly striping the characters just use a series of nested "REPLACE" statements.

    DECLARE @str1 VARCHAR(100), @str2 VARCHAR(100), @str3 VARCHAR(100)

    SET @str1 = 'A & B Cleaners'

    SET @str2 = 'A and B Cleaners'

    SET @str3 = 'Cleaners A & B'

    SELECT '*'+@str1+'*', '*'+REPLACE(REPLACE(@str1, ' ', ''), '&', '')+'*'

    SELECT '*'+@str2+'*', '*'+REPLACE(REPLACE(@str2, ' ', ''), '&', '')+'*'

    SELECT '*'+@str3+'*', '*'+REPLACE(REPLACE(@str3, ' ', ''), '&', '')+'*'

    String Result

    *A & B Cleaners* *ABCleaners*

    *A and B Cleaners* *AandBCleaners*

    *Cleaners A & B* *CleanersAB*

  • GSquared

    SSC Guru

    Points: 260824

    Stephen, you do realize this question was from May, 2 years ago (2009), right?

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 715079

    closing this thread. Please check dates before replying.

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

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