select string removing two sets of characters

  • Hi  ,

    I need help replacing a certain strings from a query response.

    My searches are pointing to a replace in the actual table but I am looking for sql that returns a different value than what is in the table.

    I am selecting a varchar(25) field and want to return the characters without the starting prefixes of D00 or 00 from the field.

    1. For example if an ID is D00241 it should return 241 .

    or

    2. For example if an ID is 00242 it should return 242.

     

     

  • SELECT SUBSTRING(ID, ID_start_byte_2, 50) AS ID_you_wanted, ID AS original_ID

    FROM dbo.table_name tn

    CROSS APPLY ( SELECT CASE WHEN LEFT(ID, 1) = 'D' THEN 2 ELSE 1 END AS ID_start_btye_1 ) AS ca1

    CROSS APPLY (SELECT CASE WHEN SUBSTRING(ID, ID_start_byte_1, 2) = '00' THEN ID_start_byte_1 + 2 ELSE ID_start_byte _1 END AS ID_start_byte_2 ) AS ca2

    SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

  • DROP TABLE IF EXISTS #SomeString;

    CREATE TABLE #SomeString
    (
    Str1 VARCHAR(25)
    );

    INSERT #SomeString
    (
    Str1
    )
    VALUES
    ('D00241')
    ,('00242 ')
    ,('Chicken Tikka Masala');

    SELECT ss.Str1
    ,CASE
    WHEN ss.Str1 LIKE 'D00%' THEN
    STUFF (ss.Str1, 1, 3, '')
    WHEN ss.Str1 LIKE '00%' THEN
    STUFF (ss.Str1, 1, 2, '')
    ELSE
    ss.Str1
    END
    FROM #SomeString ss;

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • The proposed solutions seem to be a bit more code than is needed:

    SELECT *
    , ActualID = substring(ss.Str1, patindex('%[1-9]%', ss.Str1), 25)
    FROM #SomeString ss

     

     

    Jeffrey Williams
    “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

    ― Charles R. Swindoll

    How to post questions to get better answers faster
    Managing Transaction Logs

  • Jeffrey Williams wrote:

    The proposed solutions seem to be a bit more code than is needed:

    SELECT *
    , ActualID = substring(ss.Str1, patindex('%[1-9]%', ss.Str1), 25)
    FROM #SomeString ss

    Perhaps.

    Scott's solution and mine more accurately match the specific requirement. Yours left-truncates 'E00241', for example

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • Phil Parkin wrote:

    Jeffrey Williams wrote:

    The proposed solutions seem to be a bit more code than is needed:

    SELECT *
    , ActualID = substring(ss.Str1, patindex('%[1-9]%', ss.Str1), 25)
    FROM #SomeString ss

    Perhaps.

    Scott's solution and mine more accurately match the specific requirement. Yours left-truncates 'E00241', for example

    Okay - if we only want to affect those rows that specifically start with those prefixes:

    SELECT *
    , ActualID = CASE WHEN ss.Str1 LIKE '00%' OR ss.Str1 LIKE 'D00%'
    THEN substring(ss.Str1, patindex('%[1-9]%', ss.Str1), 25)
    ELSE ss.Str1
    END
    FROM #SomeString ss

    Still simpler that multiple STUFF's depending on which prefix is found - or stacked CROSS APPLY to get the start position for a SUBSTRING.

    My solution would handle any prefix - yours and Scott's would need to be modified if a different prefix needs to be included or the actual data can contain many different prefixes and the OP was just giving an example of one of those.

     

     

    Jeffrey Williams
    “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

    ― Charles R. Swindoll

    How to post questions to get better answers faster
    Managing Transaction Logs

  • It really depends on the data.

    Try injecting ''00X11' into your version. It removes the 'X', which is not one of the specified requirements. I'm all for elegant code, and maybe the data is such that your solution works well. But without knowing all the data, maybe not.

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

Viewing 7 posts - 1 through 6 (of 6 total)

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