SQL Function to remove excess characters

  • Hello all,

    I am looking for a function or way to return only results which does not include appended characters to order numbers.

    For instance, below is a list of order numbers. I only want the order number that is SO-123456

    OrderNumbers

    SO-123456

    SO-123456-01

    SO-123456-2

    SO-123457

    SO-123457-1

    SO-123457-02

    SO-123458

    I would like my query to only show the below results

    SO-123456

    SO-123457

    SO-123458

    What functions or query methods could achieve this?

    I was hoping for something similar to RTRIM but that is only specific to white space.

    Any suggestions are welcome.

  • You need CHARINDEX() to find the position of the second dash (if it exists).

    This almost works:

    use tempdb;

    go

    SELECT OrderNo

    , CHARINDEX('-',OrderNo,4) AS FindDash

    , LEFT(OrderNo,CHARINDEX('-',OrderNo,4)) AS NoSuffix

    FROM (

    SELECT 'SO-123456' AS OrderNo

    UNION ALL

    SELECT 'SO-123456-01'

    UNION ALL

    SELECT 'SO-123456-2'

    UNION ALL

    SELECT 'SO-123457'

    UNION ALL

    SELECT 'SO-123457-1'

    UNION ALL

    SELECT 'SO-123457-02'

    UNION ALL

    SELECT 'SO-123458') x

    If you set up your question so that people can answer it really easily, (by providing readily consumable code/SELECT statements), you'll get more answers, because people won't have to work as hard to solve your problem. I usually find that setting up the question properly gets me most of the way to the answer too.

    HTH,

    Pieter

  • Slight fix to the code

    use tempdb;

    go

    SELECT OrderNo

    , CHARINDEX('-',OrderNo,4) AS FindDash

    , CASE WHEN LEFT(OrderNo,CHARINDEX('-',OrderNo,4)) =''

    THEN OrderNo

    ELSE LEFT(OrderNo,CHARINDEX('-',OrderNo,4))

    END AS NoSuffix

    FROM (

    SELECT 'SO-123456' AS OrderNo

    UNION ALL

    SELECT 'SO-123456-01'

    UNION ALL

    SELECT 'SO-123456-2'

    UNION ALL

    SELECT 'SO-123457'

    UNION ALL

    SELECT 'SO-123457-1'

    UNION ALL

    SELECT 'SO-123457-02'

    UNION ALL

    SELECT 'SO-123458') x

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • I'll tweak it a little more

    SELECT distinct CASE WHEN LEFT(OrderNo,CHARINDEX('-',OrderNo,4)) =''

    THEN OrderNo

    ELSE substring(OrderNo,1,CHARINDEX('-',OrderNo,4)-1)

    END AS NoSuffix

    FROM (

    SELECT 'SO-123456' AS OrderNo

    UNION ALL

    SELECT 'SO-123456-01'

    UNION ALL

    SELECT 'SO-123456-2'

    UNION ALL

    SELECT 'SO-123457'

    UNION ALL

    SELECT 'SO-123457-1'

    UNION ALL

    SELECT 'SO-123457-02'

    UNION ALL

    SELECT 'SO-123458') x

    but based on the sample data if your order numbers are always in the format xx-xxxxxx-xx then a simple substring would work

    select substring(OrderNo,1,9) from (

    SELECT 'SO-123456' AS OrderNo

    UNION ALL

    SELECT 'SO-123456-01'

    UNION ALL

    SELECT 'SO-123456-2'

    UNION ALL

    SELECT 'SO-123457'

    UNION ALL

    SELECT 'SO-123457-1'

    UNION ALL

    SELECT 'SO-123457-02'

    UNION ALL

    SELECT 'SO-123458') x

    group by substring(OrderNo,1,9) ;

    Bob
    -----------------------------------------------------------------------------
    How to post to get the best help[/url]

  • This could be done in a possibly more general fashion using DelimitedSplit8K but if you've only got 1 or 2 hyphens, you can also do it like this:

    WITH SampleData (OrderNo) AS

    (

    SELECT 'SO-123456' AS OrderNo

    UNION ALL SELECT 'SO-123456-01'

    UNION ALL SELECT 'SO-123456-2'

    UNION ALL SELECT 'SO-123457'

    UNION ALL SELECT 'SO-123457-1'

    UNION ALL SELECT 'SO-123457-02'

    UNION ALL SELECT 'SO-123458'

    )

    SELECT OrderNo, NewOrderNo

    FROM SampleData a

    CROSS APPLY

    (

    SELECT p1=CHARINDEX('-', OrderNo)

    ,s1=RIGHT(OrderNo, LEN(OrderNo)-CHARINDEX('-', OrderNo))

    ) b

    CROSS APPLY

    (

    SELECT NewOrderNo=LEFT(OrderNo, p1+CHARINDEX('-', s1+'-')-1)

    ) c;

    Edit: Note that this also works if there are more than two characters ahead of the first hyphen.


    My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

    My thought question: Have you ever been told that your query runs too fast?

    My advice:
    INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
    The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

    Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
    Since random numbers are too important to be left to chance, let's generate some![/url]
    Learn to understand recursive CTEs by example.[/url]
    [url url=http://www.sqlservercentral.com/articles/St

  • And yet another variation:-)

    SELECT DISTINCT LEFT(OrderNo, ISNULL(NULLIF(CHARINDEX('-',OrderNo,CHARINDEX('-',OrderNo)+1) - 1,-1),LEN(OrderNo)))

    FROM (

    SELECT 'SO-123456' AS OrderNo

    UNION ALL

    SELECT 'SO-123456-01'

    UNION ALL

    SELECT 'SO-123456-2'

    UNION ALL

    SELECT 'SO-123457'

    UNION ALL

    SELECT 'SO-123457-1'

    UNION ALL

    SELECT 'SO-123457-02'

    UNION ALL

    SELECT 'SO-123458') x

  • dwain.c (3/5/2014)


    This could be done in a possibly more general fashion using DelimitedSplit8K but if you've only got 1 or 2 hyphens, you can also do it like this:

    WITH SampleData (OrderNo) AS

    (

    SELECT 'SO-123456' AS OrderNo

    UNION ALL SELECT 'SO-123456-01'

    UNION ALL SELECT 'SO-123456-2'

    UNION ALL SELECT 'SO-123457'

    UNION ALL SELECT 'SO-123457-1'

    UNION ALL SELECT 'SO-123457-02'

    UNION ALL SELECT 'SO-123458'

    )

    SELECT OrderNo, NewOrderNo

    FROM SampleData a

    CROSS APPLY

    (

    SELECT p1=CHARINDEX('-', OrderNo)

    ,s1=RIGHT(OrderNo, LEN(OrderNo)-CHARINDEX('-', OrderNo))

    ) b

    CROSS APPLY

    (

    SELECT NewOrderNo=LEFT(OrderNo, p1+CHARINDEX('-', s1+'-')-1)

    ) c;

    Edit: Note that this also works if there are more than two characters ahead of the first hyphen.

    Nice!

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • SQLRNNR (3/6/2014)


    dwain.c (3/5/2014)


    This could be done in a possibly more general fashion using DelimitedSplit8K but if you've only got 1 or 2 hyphens, you can also do it like this:

    WITH SampleData (OrderNo) AS

    (

    SELECT 'SO-123456' AS OrderNo

    UNION ALL SELECT 'SO-123456-01'

    UNION ALL SELECT 'SO-123456-2'

    UNION ALL SELECT 'SO-123457'

    UNION ALL SELECT 'SO-123457-1'

    UNION ALL SELECT 'SO-123457-02'

    UNION ALL SELECT 'SO-123458'

    )

    SELECT OrderNo, NewOrderNo

    FROM SampleData a

    CROSS APPLY

    (

    SELECT p1=CHARINDEX('-', OrderNo)

    ,s1=RIGHT(OrderNo, LEN(OrderNo)-CHARINDEX('-', OrderNo))

    ) b

    CROSS APPLY

    (

    SELECT NewOrderNo=LEFT(OrderNo, p1+CHARINDEX('-', s1+'-')-1)

    ) c;

    Edit: Note that this also works if there are more than two characters ahead of the first hyphen.

    Nice!

    Gee whiz, thanks! And I didn't even GROUP BY NewOrderNo to get only the DISTINCTs!

    Perhaps I just wanted to leave a little fun for the OP. 😛


    My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

    My thought question: Have you ever been told that your query runs too fast?

    My advice:
    INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
    The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

    Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
    Since random numbers are too important to be left to chance, let's generate some![/url]
    Learn to understand recursive CTEs by example.[/url]
    [url url=http://www.sqlservercentral.com/articles/St

  • dwain, your code is superb one..

    Please write the UPDATE syntax also, if want to update the column.

  • Junglee_George (3/11/2014)


    dwain, your code is superb one..

    Please write the UPDATE syntax also, if want to update the column.

    Do you mean like this?

    WITH SampleData (OrderNo) AS

    (

    SELECT 'SO-123456' AS OrderNo

    UNION ALL SELECT 'SO-123456-01'

    UNION ALL SELECT 'SO-123456-2'

    UNION ALL SELECT 'SO-123457'

    UNION ALL SELECT 'SO-123457-1'

    UNION ALL SELECT 'SO-123457-02'

    UNION ALL SELECT 'SO-123458'

    )

    SELECT OrderNo

    INTO #Table

    FROM SampleData;

    UPDATE a

    SET OrderNo = NewOrderNo

    FROM #Table a

    CROSS APPLY

    (

    SELECT p1=CHARINDEX('-', OrderNo)

    ,s1=RIGHT(OrderNo, LEN(OrderNo)-CHARINDEX('-', OrderNo))

    ) b

    CROSS APPLY

    (

    SELECT NewOrderNo=LEFT(OrderNo, p1+CHARINDEX('-', s1+'-')-1)

    ) c;

    SELECT *

    FROM #Table;

    GO

    DROP TABLE #Table;


    My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

    My thought question: Have you ever been told that your query runs too fast?

    My advice:
    INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
    The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

    Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
    Since random numbers are too important to be left to chance, let's generate some![/url]
    Learn to understand recursive CTEs by example.[/url]
    [url url=http://www.sqlservercentral.com/articles/St

  • Thanks all. Your SQL skills humble me all the time. Amazing detail as always. I am so thankful to be part of such a community.

  • SELECT 'SO-123456' AS OrderNo into #test

    UNION ALL

    SELECT 'SO-123456-01'

    UNION ALL

    SELECT 'SO-123456-2'

    UNION ALL

    SELECT 'SO-123457'

    UNION ALL

    SELECT 'SO-123457-1'

    UNION ALL

    SELECT 'SO-123457-02'

    UNION ALL

    SELECT 'SO-123458'

    union all

    select 'SO-123459=02'

    select * from #test where OrderNo like '%[^A-Z]-[0-9]%'

    DROP table #test

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

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