August 15, 2014 at 6:47 pm
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
Change is inevitable... Change for the better is not.
October 14, 2014 at 11:40 am
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
October 14, 2014 at 12:35 pm
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/[/url]
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/
October 16, 2014 at 12:55 pm
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(.
SELECT
original_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_number
FROM ( --your_table_name
SELECT 'asdf - 10' AS original_string UNION ALL
SELECT 'asdf - jeik - 10' UNION ALL
SELECT 'AA - Radiator - W337'
UNION ALL SELECT 'cust_name_only_oops!' --sample of no cust#/last dash found
) AS test_data
CROSS APPLY (
SELECT CHARINDEX('-', REVERSE(original_string)) AS byte#_of_last_dash
) AS assign_alias_names
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
October 16, 2014 at 2:32 pm
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 19 (of 19 total)
You must be logged in to reply to this topic. Login to reply