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/%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/
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) "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".
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 20 (of 20 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy