Split string to show only customer

  • The data we are getting from our shipping company has the customer name and customer number attached.

    so we could have.. declare @String varchar(25) = 'asdf - 10'

    but we also have.. declare @String varchar(25) = 'asdf - jeik - 10'

    So how do I strip off the " - 10", when the ending number is not the same number of char's (1,11,111,1111, ect)

    I need to match this up with our customer table... on Customer Name.

  • Here is one way.

    declare @Table table (someString varchar(25))

    insert @Table

    select 'asdf - 10' union all

    select 'asdf - jeik - 10'

    select left(someString, len(someString) - charindex('-', REVERSE(someString)) - 1)

    from @Table

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • dwilliscp (8/7/2014)


    The data we are getting from our shipping company has the customer name and customer number attached.

    so we could have.. declare @String varchar(25) = 'asdf - 10'

    but we also have.. declare @String varchar(25) = 'asdf - jeik - 10'

    So how do I strip off the " - 10", when the ending number is not the same number of char's (1,11,111,1111, ect)

    I need to match this up with our customer table... on Customer Name.

    Do the strings always end with a dash, space, numeric value?

    If so, using the setup from Sean, look at the code for the second column.

    declare @Table table (someString varchar(25))

    insert @Table

    select 'asdf - 10' union all

    select 'asdf - jeik - 10'

    select

    left(someString, len(someString) - charindex('-', REVERSE(someString)) - 1),

    left(someString, patindex('%- [0-9]%', someString) - 1) -- << Here is another option

    from @Table

  • [/quote]

    Do the strings always end with a dash, space, numeric value?

    If so, using the setup from Sean, look at the code for the second column.

    declare @Table table (someString varchar(25))

    insert @Table

    select 'asdf - 10' union all

    select 'asdf - jeik - 10'

    select

    left(someString, len(someString) - charindex('-', REVERSE(someString)) - 1),

    left(someString, patindex('%- [0-9]%', someString) - 1) -- << Here is another option

    from @Table

    [/quote]

    Lynn,

    Well I thought it did.. but as it turns out today's extract has some letters in two of the rows. (W337 is one example)

    This does work... but not sure if I understand how...

    left( --- this is starting from the left side of the string

    Len(string - Charindex -- This is taking the string and counting the total char's in the string - the place where the '-' is located.. and since you used the "Reverse" it is doing this count from the right to the left.

    The "-1" at the end is cutting off the string just before it reaches the last '-' in the string.

  • dwilliscp (8/13/2014)


    Do the strings always end with a dash, space, numeric value?

    If so, using the setup from Sean, look at the code for the second column.

    declare @Table table (someString varchar(25))

    insert @Table

    select 'asdf - 10' union all

    select 'asdf - jeik - 10'

    select

    left(someString, len(someString) - charindex('-', REVERSE(someString)) - 1),

    left(someString, patindex('%- [0-9]%', someString) - 1) -- << Here is another option

    from @Table

    [/quote]

    Lynn,

    Well I thought it did.. but as it turns out today's extract has some letters in two of the rows. (W337 is one example)

    This does work... but not sure if I understand how...

    left( --- this is starting from the left side of the string

    Len(string - Charindex -- This is taking the string and counting the total char's in the string - the place where the '-' is located.. and since you used the "Reverse" it is doing this count from the right to the left.

    The "-1" at the end is cutting off the string just before it reaches the last '-' in the string.[/quote]

    What can you tell us about the values at the end of the string? Are they all of the same length perhaps?

  • dwilliscp (8/13/2014)


    Do the strings always end with a dash, space, numeric value?

    If so, using the setup from Sean, look at the code for the second column.

    declare @Table table (someString varchar(25))

    insert @Table

    select 'asdf - 10' union all

    select 'asdf - jeik - 10'

    select

    left(someString, len(someString) - charindex('-', REVERSE(someString)) - 1),

    left(someString, patindex('%- [0-9]%', someString) - 1) -- << Here is another option

    from @Table

    Lynn,

    Well I thought it did.. but as it turns out today's extract has some letters in two of the rows. (W337 is one example)

    This does work... but not sure if I understand how...

    left( --- this is starting from the left side of the string

    Len(string - Charindex -- This is taking the string and counting the total char's in the string - the place where the '-' is located.. and since you used the "Reverse" it is doing this count from the right to the left.

    The "-1" at the end is cutting off the string just before it reaches the last '-' in the string.

    IF the customer number is contiguous and is ALWAYS at the far right of the string, then the following will work no matter what the format of the characters are to the left of it provided that there are no interceding numeric digits.

    WITH

    cteTestData(SomeString) AS

    ( --=== This is just test data and is NOT a part of the solution

    SELECT 'asdf - 10' UNION ALL

    SELECT 'asdf - jeik - 200' UNION ALL

    SELECT 'W337'

    ) --=== This is the solution

    SELECT SUBSTRING(SomeString,PATINDEX('%[0-9]%',SomeString),25)

    FROM cteTestData

    ;

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

    Dear Lord... I'm a production DBA. Please grant me patience because, if you grant me strength, I'm gonna need bail money to go with it.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Jeff Moden (8/13/2014)


    dwilliscp (8/13/2014)


    Do the strings always end with a dash, space, numeric value?

    If so, using the setup from Sean, look at the code for the second column.

    declare @Table table (someString varchar(25))

    insert @Table

    select 'asdf - 10' union all

    select 'asdf - jeik - 10'

    select

    left(someString, len(someString) - charindex('-', REVERSE(someString)) - 1),

    left(someString, patindex('%- [0-9]%', someString) - 1) -- << Here is another option

    from @Table

    Lynn,

    Well I thought it did.. but as it turns out today's extract has some letters in two of the rows. (W337 is one example)

    This does work... but not sure if I understand how...

    left( --- this is starting from the left side of the string

    Len(string - Charindex -- This is taking the string and counting the total char's in the string - the place where the '-' is located.. and since you used the "Reverse" it is doing this count from the right to the left.

    The "-1" at the end is cutting off the string just before it reaches the last '-' in the string.

    IF the customer number is contiguous and is ALWAYS at the far right of the string, then the following will work no matter what the format of the characters are to the left of it provided that there are no interceding numeric digits.

    WITH

    cteTestData(SomeString) AS

    ( --=== This is just test data and is NOT a part of the solution

    SELECT 'asdf - 10' UNION ALL

    SELECT 'asdf - jeik - 200' UNION ALL

    SELECT 'W337'

    ) --=== This is the solution

    SELECT SUBSTRING(SomeString,PATINDEX('%[0-9]%',SomeString),25)

    FROM cteTestData

    ;

    Actually, Jeff, the OP is trying to strip off everything from the last - to end of the string.

  • Thanks, Lynn. I read the original post incorrectly and didn't run your code to see. Thanks for the correction.

    I must admit, I'm totally confused. If the purpose is to "match up to the customer name table", what name would W337 match up to after stripping off the number?

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

    Dear Lord... I'm a production DBA. Please grant me patience because, if you grant me strength, I'm gonna need bail money to go with it.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • You are welcome. It is also why I asked for a more detailed explanation of how the string ends. The more we know the structure the easier to figure out the pattern to search for at the end.

  • Jeff Moden (8/13/2014)


    Thanks, Lynn. I read the original post incorrectly and didn't run your code to see. Thanks for the correction.

    I must admit, I'm totally confused. If the purpose is to "match up to the customer name table", what name would W337 match up to after stripping off the number?

    Jeff the W337, comes where the customer number is... SAP allows for the customer number to be Char.. so we have a few that are not numbers. So in this case you would see AA - Radiator - W337. And you would want:

    Customer_Number : W337

    Customer_Name: AA - Radiator

    .

    Also note that the Customer Number is not always the same number of Cha's, thus you must search for the last '-' in the string and grab everything to the right of the last '-'.

    As a side note the Customer Name, that we are getting here, from the shipping company, is a text entered field... so the customer name does not always match exactly what we have on file... so I am pulling out the customer number and then looking up the customer name in our data extracted out of SAP and into our Data Warehouse.

  • dwilliscp (8/14/2014)


    Jeff Moden (8/13/2014)


    Thanks, Lynn. I read the original post incorrectly and didn't run your code to see. Thanks for the correction.

    I must admit, I'm totally confused. If the purpose is to "match up to the customer name table", what name would W337 match up to after stripping off the number?

    Jeff the W337, comes where the customer number is... SAP allows for the customer number to be Char.. so we have a few that are not numbers. So in this case you would see AA - Radiator - W337. And you would want:

    Customer_Number : W337

    Customer_Name: AA - Radiator

    .

    Also note that the Customer Number is not always the same number of Cha's, thus you must search for the last '-' in the string and grab everything to the right of the last '-'.

    As a side note the Customer Name, that we are getting here, from the shipping company, is a text entered field... so the customer name does not always match exactly what we have on file... so I am pulling out the customer number and then looking up the customer name in our data extracted out of SAP and into our Data Warehouse.

    Ok. So ALL the data uses the 2 part naming convention and will ALWAYS have two dashes in them? If so, the solutions that use REVERSE to find the second dash will work just fine.

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

    Dear Lord... I'm a production DBA. Please grant me patience because, if you grant me strength, I'm gonna need bail money to go with it.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Jeff Moden (8/14/2014)


    dwilliscp (8/14/2014)


    Jeff Moden (8/13/2014)


    Thanks, Lynn. I read the original post incorrectly and didn't run your code to see. Thanks for the correction.

    I must admit, I'm totally confused. If the purpose is to "match up to the customer name table", what name would W337 match up to after stripping off the number?

    Jeff the W337, comes where the customer number is... SAP allows for the customer number to be Char.. so we have a few that are not numbers. So in this case you would see AA - Radiator - W337. And you would want:

    Customer_Number : W337

    Customer_Name: AA - Radiator

    .

    Also note that the Customer Number is not always the same number of Cha's, thus you must search for the last '-' in the string and grab everything to the right of the last '-'.

    As a side note the Customer Name, that we are getting here, from the shipping company, is a text entered field... so the customer name does not always match exactly what we have on file... so I am pulling out the customer number and then looking up the customer name in our data extracted out of SAP and into our Data Warehouse.

    Ok. So ALL the data uses the 2 part naming convention and will ALWAYS have two dashes in them? If so, the solutions that use REVERSE to find the second dash will work just fine.

    Yes, it would. But I always like to look for alternatives. If the customer number at the end is a fixed size a simple pattern could also be used to find the customer number at the end and drop it. The key is knowing what this pattern is for the customer number. If the length of the customer number is variable, then it becomes a bit more difficult and the REVERSE function becomes the more valuable solution.

  • Lynn Pettis (8/14/2014)


    Jeff Moden (8/14/2014)


    dwilliscp (8/14/2014)


    Jeff Moden (8/13/2014)


    Thanks, Lynn. I read the original post incorrectly and didn't run your code to see. Thanks for the correction.

    I must admit, I'm totally confused. If the purpose is to "match up to the customer name table", what name would W337 match up to after stripping off the number?

    Jeff the W337, comes where the customer number is... SAP allows for the customer number to be Char.. so we have a few that are not numbers. So in this case you would see AA - Radiator - W337. And you would want:

    Customer_Number : W337

    Customer_Name: AA - Radiator

    .

    Also note that the Customer Number is not always the same number of Cha's, thus you must search for the last '-' in the string and grab everything to the right of the last '-'.

    As a side note the Customer Name, that we are getting here, from the shipping company, is a text entered field... so the customer name does not always match exactly what we have on file... so I am pulling out the customer number and then looking up the customer name in our data extracted out of SAP and into our Data Warehouse.

    Ok. So ALL the data uses the 2 part naming convention and will ALWAYS have two dashes in them? If so, the solutions that use REVERSE to find the second dash will work just fine.

    Yes, it would. But I always like to look for alternatives. If the customer number at the end is a fixed size a simple pattern could also be used to find the customer number at the end and drop it. The key is knowing what this pattern is for the customer number. If the length of the customer number is variable, then it becomes a bit more difficult and the REVERSE function becomes the more valuable solution.

    I was responding to the OP. 😉 I'm like you... always looking for alternatives.

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

    Dear Lord... I'm a production DBA. Please grant me patience because, if you grant me strength, I'm gonna need bail money to go with it.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Lynn Pettis (8/14/2014)


    Yes, it would. But I always like to look for alternatives. If the customer number at the end is a fixed size a simple pattern could also be used to find the customer number at the end and drop it. The key is knowing what this pattern is for the customer number. If the length of the customer number is variable, then it becomes a bit more difficult and the REVERSE function becomes the more valuable solution.

    Well, we know from the original post that the number of digits in the number isn't fixed (it's explicitly stated) so the solution using reverse is the was to go.

    But even if the number has a fixed number of digits a simple pattern won't do the trick unless it can be guaranteed that no cutomer name can start with a digit (or if a naive pattern is used, that no customer name can contain digits) and we don't have that information, so we would have to use reverse anyway.

    Tom

  • If you want another alternative

    LTRIM(PARSENAME(REPLACE(SomeString,'-','.'),1))

    😀

    *Edited*

    p.s. BTW, before you start, I did not say it was a good alternative 😛

    Far away is close at hand in the images of elsewhere.
    Anon.

Viewing 15 posts - 1 through 15 (of 19 total)

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