Special Characters

  • 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

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

    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)

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

    Thanks

    Jim

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

    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)

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

    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)

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

    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)

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

  • 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

  • closing this thread. Please check dates before replying.

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

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