March 7, 2014 at 9:32 am
Hi,
I need help parsing out a field, but I am not sure how to extract just the specific part that I need.
Examples:
ELA-DUPLECHEIN, KELLIE-13-14-2(A-B)
READING -LANDRY, MICHELE B-13-14-2(A)
What I want to do is extract just the name from this. For the examples above I am looking to return:
DUPLECHEIN, KELLIE and LANDRY, MICHELE
Please let me know a solution to this.
Thanks
March 7, 2014 at 10:40 am
skaggs.andrew (3/7/2014)
Hi,I need help parsing out a field, but I am not sure how to extract just the specific part that I need.
Examples:
ELA-DUPLECHEIN, KELLIE-13-14-2(A-B)
READING -LANDRY, MICHELE B-13-14-2(A)
What I want to do is extract just the name from this. For the examples above I am looking to return:
DUPLECHEIN, KELLIE and LANDRY, MICHELE
Please let me know a solution to this.
Thanks
it looks as if hyphen is inconsistently the delimiter of these text rows - correct?
Get this function: http://blogs.interfacett.com/fn_split-table-valued-function-by-sql-instructor-jeff-jones
and then
select [value] from dbo.fn_split([the string row], '-') where idx=2
March 7, 2014 at 11:51 am
I agree with JohnFTamburo on using split function and the probable inconsistency of hypen in the text... still wanted to give a shot so ended up writing following ... wasn't able deal with last value MICHELE as B is appearing in it.. if there exists a hypen before B then it should give you the result
;with cte(str) as
(
select 'ELA-DUPLECHEIN' union all
select 'KELLIE-13-14-2(A-B)' union all
select 'READING -LANDRY' union all
select 'MICHELE B-13-14-2(A'
)
select *,
case
when charindex('-',SUBSTRING(str,CHARINDEX('-',str,1)+1,LEN(str))) = 0 then
SUBSTRING(str,CHARINDEX('-',str,1)+1,LEN(str))
else SUBSTRING(str,1,CHARINDEX('-',str,1)-1)
end as parsed from cte
March 7, 2014 at 11:58 am
SSC-Addicted When I try to use that case statement, it seems to be returning fragments left of the first '-'
March 7, 2014 at 12:17 pm
is it ? can you please try to execute the entire query again.. isn't it returning the expected result = DUPLECHEIN, KELLIE and LANDRY, MICHELE..of course except for MICHELE..
The logic that I implemented is when number of hyphend are >1 then get the string located @ left of the hyphen else return string on the right hand side of hyphen
March 7, 2014 at 12:25 pm
JohnFTamburo (3/7/2014)
skaggs.andrew (3/7/2014)
Hi,I need help parsing out a field, but I am not sure how to extract just the specific part that I need.
Examples:
ELA-DUPLECHEIN, KELLIE-13-14-2(A-B)
READING -LANDRY, MICHELE B-13-14-2(A)
What I want to do is extract just the name from this. For the examples above I am looking to return:
DUPLECHEIN, KELLIE and LANDRY, MICHELE
Please let me know a solution to this.
Thanks
it looks as if hyphen is inconsistently the delimiter of these text rows - correct?
Get this function: http://blogs.interfacett.com/fn_split-table-valued-function-by-sql-instructor-jeff-jones
and then
select [value] from dbo.fn_split([the string row], '-') where idx=2
Yes a sql string splitter is what is needed but I would HIGHLY recommend NOT using this one. It is a combination of very slow ways to do this. First of all it is using a loop to perform the split. Secondly it is multi line table valued function which at best will perform as poorly as a scalar function, often times even worse.
If you want a better splitter take a look at the link in my signature about splitting strings. It will blow the doors of that MTVF for performance.
_______________________________________________________________
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/
March 7, 2014 at 12:38 pm
Hi. I read Moden's splitter article. You are correct. It is stinking brilliant! I'm typing this at lunch on my iPhone. But I will be testing this sucker and I believe that it will be the new standard here.
Thanks!
John.
March 7, 2014 at 12:42 pm
Thanks for the feedback all. I am going to check out that article now.
March 7, 2014 at 12:45 pm
DECLARE @t TABLE (line varchar(100));
INSERT into @t VALUES ('ELA-DUPLECHEIN, KELLIE-13-14-2(A-B)');
INSERT INTO @t VALUES ('READING -LANDRY, MICHELE B-13-14-2(A)');
SELECT
CASE WHEN a > 0 AND b> 0 THEN SUBSTRING(line, a+1, b-1) ELSE line end
FROM
(
SELECT CHARINDEX('-', line) AS a, CHARINDEX('-', RIGHT(line, LEN(line) - CHARINDEX('-', line))) AS b, line
FROM @t t
) AS x
;
March 7, 2014 at 5:51 pm
Hi
Not sure if I have the rules correct here, but I have applied the following:
Strip anything before and including the first hyphen
Strip anything after and including the second hyphen and any single alphanumeric before it
-- Strip characters from the end
SELECT
STUFF(s1,
COALESCE(
NULLIF(PATINDEX('% [a-z0-9]-%',s1),0),
CHARINDEX('-',s1)
),
LEN(s1),
''
) [result]
FROM (
-- Strip characters from the beginning
SELECT
STUFF(s,1,CHARINDEX('-',S),'') s1
FROM (VALUES
('ELA-DUPLECHEIN, KELLIE-13-14-2(A-B)'),
('READING -LANDRY, MICHELE B-13-14-2(A)')
) A(S)
) B(s1)
March 9, 2014 at 6:34 pm
You can also do this using Cascading CROSS APPLYs:
WITH SampleData (Str2Split) AS
(
SELECT 'ELA-DUPLECHEIN, KELLIE-13-14-2(A-B)'
UNION ALL SELECT 'READING -LANDRY, MICHELE B-13-14-2(A)'
)
SELECT Str2Split, s2
FROM SampleData a
CROSS APPLY
(
SELECT s1=SUBSTRING(Str2Split, 1+CHARINDEX('-', Str2Split), 999)
) b
CROSS APPLY
(
SELECT s2=LEFT(s1, CHARINDEX('-', s1)-1)
) c;
Usually, with less than 4-5 splits, this approach will be faster even that DelimitedSplit8K.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
March 10, 2014 at 10:30 am
And how will you handle hyphenated last names such as "John Smythe-Barrows"? Do you have a known list of elements that might be before the first hyphen and after the last hyphen so you can program against them specfically? Or instead of searching for the second hyphen, you might need to use "second to last" hyphen (or whatever is appropriate). I don't have an exact answer for this, but it jumped out at me as a real problem for this particular query. I recently ran across someone who's last name was "Null" and that made me wonder if I had been properly allowing for ALL the possible values in some of my old queries. 🙂
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply