Split string to show only customer
-
David Burrows (8/15/2014)
If you want another alternativeLTRIM(PARSENAME(REPLACE(SomeString,'-','.'),1))
*Edited*
p.s. BTW, before you start, I did not say it was a good alternative
Heh... I love it. So simple. Throw in sections 2 and 3 and Bob's your uncle for what we know of this problem.
--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.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Here is an alternate solution to your problem...
The following recursive query locates the last delimiter and parses the record into cust_name and cust_nbr.
In the recursive query, at every iteration str_name stores the name past the delimiter..
replace ?delimiter with the character that is used to delimit in the data
with recursive cust_records(name, del_pos, str_name, last_dlmtr, last_dlmtr_pos) as
(
select name
,index(name, '?delimiter') as pos
,case when pos > 0 then substr(name, pos+1) else '' end as str_name
,case when pos <= 0 then 'Y'
when index(str_name, '?delimiter') > 0 then 'N'
else 'Y'
end as last_dlmtr
,pos as last_dlmtr_pos
from my_customers
union all
select name
,index(str_name, '?delimiter') as pos2
,case when pos2 > 0 then substr(str_name, pos2+1) else '' end as str_other
,case when pos2 <= 0 then 'Y'
when index(str_other, '?delimiter') > 0 then 'N'
else 'Y'
end as last_dlmtr
,last_dlmtr_pos + pos2 as last_dlmtr_pos2
from cust_records
where pos2 > 0
)
select case when last_dlmtr_pos > 0 then trim(substr(name, 1, last_dlmtr_pos-1)) else trim(name) end as cust_name
,case when last_dlmtr_pos > 0 then trim(substr(name, last_dlmtr_pos+1)) end as cust_nbr
from cust_records
where last_dlmtr = 'Y'
Hope this helps
-
VSP (10/14/2014)
Here is an alternate solution to your problem...The following recursive query locates the last delimiter and parses the record into cust_name and cust_nbr.
In the recursive query, at every iteration str_name stores the name past the delimiter..
replace ?delimiter with the character that is used to delimit in the data
with recursive cust_records(name, del_pos, str_name, last_dlmtr, last_dlmtr_pos) as
(
select name
,index(name, '?delimiter') as pos
,case when pos > 0 then substr(name, pos+1) else '' end as str_name
,case when pos <= 0 then 'Y'
when index(str_name, '?delimiter') > 0 then 'N'
else 'Y'
end as last_dlmtr
,pos as last_dlmtr_pos
from my_customers
union all
select name
,index(str_name, '?delimiter') as pos2
,case when pos2 > 0 then substr(str_name, pos2+1) else '' end as str_other
,case when pos2 <= 0 then 'Y'
when index(str_other, '?delimiter') > 0 then 'N'
else 'Y'
end as last_dlmtr
,last_dlmtr_pos + pos2 as last_dlmtr_pos2
from cust_records
where pos2 > 0
)
select case when last_dlmtr_pos > 0 then trim(substr(name, 1, last_dlmtr_pos-1)) else trim(name) end as cust_name
,case when last_dlmtr_pos > 0 then trim(substr(name, last_dlmtr_pos+1)) end as cust_nbr
from cust_records
where last_dlmtr = 'Y'
Hope this helps
You should take a look at the article referenced many time throughout this thread. Here is the link again. http://www.sqlservercentral.com/articles/Tally+Table/72993/%5B/url%5D
The recursive cte approach will work but the performance is not very good. Look at that article, it explains the performance issues with this type of splitter.
_______________________________________________________________
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/ -
I'd think you'd definitely want to use LTRIM() and RTRIM() to make sure you get rid of extraneous spaces. I added ISNULL(NULLIF(...)...) just in case there isn't a dash customer# at the end of data; if there will always be a dash & cust#, you can remove the ISNULL(NULLIF(.
SELECToriginal_string,RTRIM(LEFT(original_string, LEN(original_string) - byte#_of_last_dash)) AS customer_name,LTRIM(RTRIM(RIGHT(original_string, ISNULL(NULLIF(byte#_of_last_dash, 0), 1) - 1))) AS customer_numberFROM ( --your_table_nameSELECT 'asdf - 10' AS original_string UNION ALLSELECT 'asdf - jeik - 10' UNION ALLSELECT 'AA - Radiator - W337'UNION ALL SELECT 'cust_name_only_oops!' --sample of no cust#/last dash found) AS test_dataCROSS APPLY (SELECT CHARINDEX('-', REVERSE(original_string)) AS byte#_of_last_dash) AS assign_alias_namesSQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
-
I believe the CHARINDEX returns 0 if the delimiter is not found in the string. Tweaked the customer number formula a little bit to handle these scenarios
CASE WHEN coalesce(byte#_of_last_dash, 0) = 0 THEN '' <<-- handles if original string is NULL or not found
WHEN byte#_of_last_dash = 1 THEN '' <<-- Handles when the delimiter is the last character in the orginal string
ELSE LTRIM(RTRIM(RIGHT(original_string, byte#_of_last_dash - 1)
END AS customer_number
Viewing 5 posts - 16 through 20 (of 20 total)
You must be logged in to reply to this topic. Login to reply