August 7, 2014 at 2:07 pm
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.
August 7, 2014 at 2:18 pm
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/
August 7, 2014 at 4:36 pm
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
August 13, 2014 at 12:33 pm
[/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.
August 13, 2014 at 1:10 pm
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?
August 13, 2014 at 7:44 pm
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
Change is inevitable... Change for the better is not.
August 13, 2014 at 8:00 pm
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.
August 13, 2014 at 8:37 pm
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
Change is inevitable... Change for the better is not.
August 13, 2014 at 8:41 pm
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.
August 14, 2014 at 6:52 am
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.
August 14, 2014 at 7:28 am
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
Change is inevitable... Change for the better is not.
August 14, 2014 at 9:59 am
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.
August 14, 2014 at 6:01 pm
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
Change is inevitable... Change for the better is not.
August 15, 2014 at 6:33 am
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
August 15, 2014 at 6:53 am
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