First Time using SUBSTRING

  • Bron Tamulis

    Ten Centuries

    Points: 1084

    Okay - I have a text field that i need to 'wildcard' search and return a string value.......I settled in on SUBSTRING and charindex and I am real close - but can not figure out why some results have space then the string i am looking for - I am hoping from the picture someone can suggest improving my case statement.........thanks in advance.......I really tried to solve myself.....

  • dkultasev

    SSC Rookie

    Points: 34

    First of all, don't add SQL code as a picture, add it as a text. There is "{;} Insert/edit code sample" button to make the code highlighted properly.

    According to your issue, then you get the position of ORD and then substract 3 from it, so you get some whitespaces as well. Most probably you have 3 spaces there and in some cases 2 of them (row #109).

    What you need to do is to something like that:

    DECLARE @test VARCHAR(100) = 'PO FT20-04-29   ORD00043330'
    SELECT
    CASE
    WHEN @test LIKE 'PO%' THEN SUBSTRING(@test, CHARINDEX('ORD', @test), 11)
    END

    If the length that code is always 11 then it is much easier to use RIGHT function:

    DECLARE @test VARCHAR(100) = 'PO FT20-04-29   ORD00043330'
    SELECT
    CASE
    WHEN @test LIKE 'PO%' THEN RIGHT(@test, 11)
    END
  • Steve Collins

    SSC Eights!

    Points: 889

    Fwiw, adding meta data like ('ORD'+number and 'MO'+date) to database keys is not the best practice.  If you're stuck with it perhaps it's possible to add constraints which only allow those characters once.  Maybe there are already constraints?

    Imo there are potential issues with searching for 'PO%'.  Without knowing the server-level collation of your database (and assuming it's the factory default SQL_Latin1_General_CP1_CI_AS), the search will be case insensitive.  Since "po" is not an uncommon letter combination...  The picture says to "grab ORD value" which seems like a safer approach.  How much checking is necessary depends on an assessment of the underlying data.  Here's checking the 4th character to make sure it's an integer.

    --declare @test varchar(100) = 'PO FT20-04-29   ORD00043330';
    --declare @test varchar(100) = 'order ORD00043330 blah blah blah';
    declare @test varchar(100) = 'ORDxx ORD00043330 blah';
    declare @test_ndx char(11) = substring(@test, charindex('ORD', @test collate sql_latin1_general_cp1_cs_as), 11);

    select
    iif(substring(@test_ndx, 4, 1) like '[0-9]', @test_ndx, 'Stock') Demand;

     

  • Bron Tamulis

    Ten Centuries

    Points: 1084

    Thanks for the reply. I will refrain from pasting picture of code.

    A little background - MO = Manufacturing Order. Dataset has one record for every Manufacturing Order. Objective is to give end user ability to filter and get all Manufacturing Order Records pertain to a single Sales Order (Always starts with ORD and always part of the string MO Description).

    The final code that works perfectly (at least so far)

    CASE WHEN A.DSCRIPTN like 'From%' then SUBSTRING(A.DSCRIPTN, 9, 11)

    WHEN A.DSCRIPTN like 'ORD%' then SUBSTRING(A.DSCRIPTN, 1, 11)

    WHEN A.DSCRIPTN like 'PO%' then SUBSTRING(A.DSCRIPTN, CHARINDEX('ORD', A.DSCRIPTN),11)

    else 'STOCK' end as Demand,

    I took out the -3 and changed the 14 to an 11.  I thought -3 was needed to go back from 'ORD' (which is 3 characters) didn't realize it was going back from the start of ORD and thus, giving me blank spaces left of the ORD value. Fortunately the ORD value is always 11 characters long - which means once you find the ORD - you get the proper value.....

    Thanks again - it's great I can use SUBSTRING again if I have to.

     

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 720484

    If you have extra spaces, once you substring, you can also use TRIM to clear things out.

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

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