Post reply

Removing consecutive characters from the middle of a string

  • April 14, 2015 at 9:26 am

    #302944

    I usually do this through Access so I'm not too familiar with the string functions in SQL. My question is, how do you remove characters from the middle of a string?

    Ex:

    String value is 10 characters long.

    The string value is X000001250.

    The end result should look like, X1250.

    I've tried mixing/matching multiple string functions with no success. The only solution I have come up with removes ALL of the zeros, including the tailing zero. The goal is to only remove the consecutive zeroes in the middle of the string.

    Any ideas? Thanks!

  • April 14, 2015 at 9:33 am

    #1790859

    Don't know whether this[/url] helps?

    John

  • April 14, 2015 at 9:38 am

    #1790864

    LEFT('X000001250',1) + RIGHT('X000001250',CHARINDEX('0',REVERSE('X000001250'),2)-1) will work to remove the mid-string zeros for a value following the X + 9 digit format.

  • April 14, 2015 at 11:53 am

    #1790926

    sKreetz! (4/14/2015)

    I usually do this through Access so I'm not too familiar with the string functions in SQL. My question is, how do you remove characters from the middle of a string?

    Ex:

    String value is 10 characters long.

    The string value is X000001250.

    The end result should look like, X1250.

    I've tried mixing/matching multiple string functions with no success. The only solution I have come up with removes ALL of the zeros, including the tailing zero. The goal is to only remove the consecutive zeroes in the middle of the string.

    Any ideas? Thanks!

    If it's ALWAYS a single non-numeric character followed by a zero-padded integer, this is simple and avoids some rather expensive functions such as REVERSE and CHARINDEX. The outer RIGHT and the "+0" are simple conversion tricks. If those are too "black artsy", feel free to change them to CAST or CONVERT.

    DECLARE @SomeString CHAR(10);

    SELECT @SomeString = 'X000001250';

    SELECT LEFT(@SomeString,1)+RIGHT(RIGHT(@SomeString,9)+0,9)

    ;

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

  • April 14, 2015 at 12:04 pm

    #1790927

    Elegant! Thanks, I learned something new today.

  • April 14, 2015 at 12:27 pm

    #1790933

    Thanks, and I really appreciate the quick responses lynncs/Jeff Moden. Both examples worked!

  • April 14, 2015 at 1:04 pm

    #1790947

    The string is always going to be 10 characters long, but what if some of them begin with 2 or even 3 alphabetical characters? How do you account for that?

  • April 14, 2015 at 1:06 pm

    #1790948

    Woops I completely forgot about Jeff Moden's answer hahaha. You can tell this thing has been driving me nuts.

  • April 14, 2015 at 1:19 pm

    #1790950

    Jeff, running this in a select statement with a from clause causes an error. "Conversion failed when converting the nvarchar value to data type int." Is the +0 throwing something off?

    SELECT LEFT(table1.column,1)+RIGHT(RIGHT(table1.column,9)+0,9) as blah

    FROM table1;

    Am not doing something right, here?

  • April 16, 2015 at 9:23 am

    #1791389

    Your table contains at least one row with either:

    i) a non-digit after the first character in that column, or

    ii) a column length less than 10.

    If the second case, try replacing the 9's with LEN(column) - 1.

    Update: SQL Server 2014 will give you a better error message, for example: Conversion failed when converting the nvarchar value 'A00005674' to data type int.

  • April 16, 2015 at 10:06 am

    #1791407

    Gary, Replacing the 9's with Len(col)-1 still raises the same error. The column will always be 10 characters long. The string can begin with 1 letter or multiple letters followed by the the inner zeroes, then followed by numbers. Ex X000001250 or XA00001250 or XAB0001250. The goal here is to just remove the inner zeroes.

  • April 16, 2015 at 10:15 am

    #1791413

    Try this modification of Jeff's suggestion. It accounts for the possibility of multiple alpha characters at the beginning of the string

    DECLARE @SomeString CHAR(10);

    SELECT @SomeString = 'XY00001250';

    SELECT LEFT(@SomeString,CHARINDEX('0',@SomeString,1)-1)+RIGHT(RIGHT(@SomeString,LEN(@SomeString)-(CHARINDEX('0',@SomeString,1)))+0,9)

    ;

  • April 16, 2015 at 4:08 pm

    #1791507

    Thanks again guys for the responses, and thank you lyncc! Does anybody know where I can find a complete list of sql functions with descriptions to study?

  • April 16, 2015 at 5:06 pm

    #1791519

    sKreetz! (4/16/2015)

    Thanks again guys for the responses, and thank you lyncc! Does anybody know where I can find a complete list of sql functions with descriptions to study?

    Yes. Sql Server used to come with a help system called "Books Online". Now you have to either download it separately or use the online version. You get there in SSMS by pressing the F1 key.

    You can also find it using your Yabingooglehoo skills. Search for "Built in functions SQL Server 2012". Here's one of the links that shows up on that search...

    https://technet.microsoft.com/en-us/library/ms174318(v=sql.110).aspx

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

Viewing 14 posts - 1 through 13 (of 13 total)

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