December 18, 2013 at 11:46 am
Hi Everyone
I am having an syntax issues I am not able to work out. I have a value in a column '01-08-087-0101W5'
I would like to break this value down into 5 different columns
the column will be broken down at the dashes , so the 5 columns will have values like
01
08
087
101
5
I would also like to trim any leading 0's in the above values. i have been trying the following
SUBSTRING(column1,patindex('%[^0]%',column1) ,2) AS FROM_TWP
Thanks.
Edit, I made a mistake in the posting. I needed 5 not 4 columns
December 18, 2013 at 11:53 am
I'm not really sure about this approach, but it's an option 😉
SELECT SUBSTRING( PARSENAME( REPLACE( string, '-', '.'), 4), PATINDEX( '%[^0]%', PARSENAME( REPLACE( string, '-', '.'), 4)), 100),
SUBSTRING( PARSENAME( REPLACE( string, '-', '.'), 3), PATINDEX( '%[^0]%', PARSENAME( REPLACE( string, '-', '.'), 3)), 100),
SUBSTRING( PARSENAME( REPLACE( string, '-', '.'), 2), PATINDEX( '%[^0]%', PARSENAME( REPLACE( string, '-', '.'), 2)), 100),
SUBSTRING( PARSENAME( REPLACE( string, '-', '.'), 1), PATINDEX( '%[^0]%', PARSENAME( REPLACE( string, '-', '.'), 1)), 100)
FROM (VALUES('01-08-087-0101WW'))x(string)
December 18, 2013 at 11:55 am
Luis Cazares (12/18/2013)
I'm not really sure about this approach, but it's an option 😉
SELECT SUBSTRING( PARSENAME( REPLACE( string, '-', '.'), 4), PATINDEX( '%[^0]%', PARSENAME( REPLACE( string, '-', '.'), 4)), 100),
SUBSTRING( PARSENAME( REPLACE( string, '-', '.'), 3), PATINDEX( '%[^0]%', PARSENAME( REPLACE( string, '-', '.'), 3)), 100),
SUBSTRING( PARSENAME( REPLACE( string, '-', '.'), 2), PATINDEX( '%[^0]%', PARSENAME( REPLACE( string, '-', '.'), 2)), 100),
SUBSTRING( PARSENAME( REPLACE( string, '-', '.'), 1), PATINDEX( '%[^0]%', PARSENAME( REPLACE( string, '-', '.'), 1)), 100)
FROM (VALUES('01-08-087-0101WW'))x(string)
I will give this a try and see.
This worked to a point. I have edit the post because I need 5 columns not 4 .
December 18, 2013 at 12:50 pm
Take a look at the link in my signature about splitting strings. This is probably the best approach for what you are trying to do here.
_______________________________________________________________
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/
December 18, 2013 at 12:52 pm
You will end up with something similar to this.
declare @Something table (SomeValue varchar(50))
insert @Something(SomeValue) values('01-08-087-0101W5')
select *
from @Something
cross apply dbo.DelimitedSplit8K(SomeValue, '-')
_______________________________________________________________
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/
December 18, 2013 at 12:52 pm
Luis's /Seans's suggestions will perform the best;
if you want to stick with a scalar function, this post has a function fn_parsename, which fidns values form left-to-right, where the original parsename does items right-to-left
that function doe snto have a limit to the number of items.
http://qa.sqlservercentral.com/Forums/FindPost1178158.aspx
remember a scalar function can degrade performance on large datasets.
you'd still ahve to remove the preceeding zeros, as well.
select
dbo.fn_parsename(MyColumn,'.',1),
dbo.fn_parsename(MyColumn,'.',2),
dbo.fn_parsename(MyColumn,'.',3),
dbo.fn_parsename(MyColumn,'.',4),
dbo.fn_parsename(MyColumn,'.',5),
dbo.fn_parsename(MyColumn,'.',6),
dbo.fn_parsename(MyColumn,'.',7),
MyColumn
FROM MySAmpleData
y
Lowell
December 18, 2013 at 1:00 pm
See if this helps (having fun with strings).
The idea is to identify the breaking positions to be used by the function SUBSTRING.
DECLARE @S varchar(128) = '01-08-087-0101W5';
SELECT
CAST(e1.c1 AS int) AS e1,
CAST(e2.c1 AS int) AS e2,
CAST(e3.c1 AS int) AS e3,
CAST(e5.c1 AS int) AS e4,
CAST(e6.c1 AS int) AS e5
FROM
(SELECT CHARINDEX('-', @S)) AS T1(c1)
OUTER APPLY
(SELECT CHARINDEX('-', @S, T1.c1 + 1)) AS T2(c1)
OUTER APPLY
(SELECT CHARINDEX('-', @S, T2.c1 + 1)) AS T3(c1)
OUTER APPLY
(SELECT SUBSTRING(@s, 1, T1.c1 - 1)) AS e1(c1)
OUTER APPLY
(SELECT SUBSTRING(@s, T1.c1 + 1, T2.c1 - T1.c1 - 1)) AS e2(c1)
OUTER APPLY
(SELECT SUBSTRING(@s, T2.c1 + 1, T3.c1 - T2.c1 - 1)) AS e3(c1)
OUTER APPLY
(SELECT SUBSTRING(@s, T3.c1 + 1, 128)) AS e4(c1)
OUTER APPLY
(SELECT PATINDEX('%[^0-9]%', e4.c1)) AS T4(c1)
OUTER APPLY
(SELECT SUBSTRING(e4.c1, 1, T4.c1 - 1)) AS e5(c1)
OUTER APPLY
(SELECT SUBSTRING(e4.c1, T4.c1 +1, 128)) AS e6(c1)
GO
December 18, 2013 at 1:06 pm
Lowell,
Would it be possible to make your function perform better by converting it to an iTVF?
December 18, 2013 at 1:26 pm
Like this?
CREATE FUNCTION dbo.fn_parsename
(
@pString VARCHAR(7999),
@pDelimiter CHAR(1),
@Occurrance int
)
RETURNS table with schemabinding
AS
return
--===== "Inline" CTE Driven "Tally Table” produces values up to
-- 10,000... enough to cover VARCHAR(8000)
WITH
E1(N) AS ( --=== Create Ten 1's
SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 --10
),
E2(N) AS (SELECT 1 FROM E1 a, E1 b), --100
E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10,000
cteTally(N) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT N)) FROM E4) ,
--===== Do the split
InterResults
AS
(
SELECT ROW_NUMBER() OVER (ORDER BY N) AS ItemNumber,
SUBSTRING(@pString, N, CHARINDEX(@pDelimiter, @pString + @pDelimiter, N) - N) AS Item
FROM cteTally
WHERE N < LEN(@pString) + 2
AND SUBSTRING(@pDelimiter + @pString, N, 1) = @pDelimiter
)
SELECT Item FROM InterResults WHERE ItemNumber = @Occurrance
Then to use Lowell's code we need a table.
create table MySampleData
(
MyColumn varchar(100)
)
insert MySampleData
select '01-08-087-0101W5'
Finally...
select *
FROM MySAmpleData
cross apply dbo.fn_parsename(MyColumn,'-',1) a
cross apply dbo.fn_parsename(MyColumn,'-',2) b
cross apply dbo.fn_parsename(MyColumn,'-',3) c
cross apply dbo.fn_parsename(MyColumn,'-',4) d
_______________________________________________________________
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/
December 18, 2013 at 5:01 pm
A very easy way to do this is with a pattern-based string splitter:
WITH SampleData (TestStr) AS
(
SELECT '01-08-087-0101W5'
)
SELECT *, DesiredResult=CAST(Item AS INT)
FROM SampleData
CROSS APPLY dbo.PatternSplitCM(TestStr, '[0-9]')
WHERE [Matched]=1;
The PatternSplitCM FUNCTION can be found in the 4th article in my signature links.
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
January 3, 2014 at 6:44 am
Ok Sean, I'm revisiting your ITVF version of this function, and seem to have a problem with it when the data does not have the data at the assumed index;
rub my nose in it and tell me what i'm doing wrong.
i renamed your version to fn_parsenameITVF, and here is my test code:
i get no results due to no 4th param found
DECLARE @Result TABLE (Serial nvarchar(10),Title varchar(20))
INSERT INTO @Result
SELECT '1.1','a' UNION ALL
SELECT '1.2.1','b' UNION ALL
SELECT '1.2.2','C' UNION ALL
SELECT '1.2.3','G' UNION ALL
SELECT '1.11','B' UNION ALL
SELECT '2.3','B' UNION ALL
SELECT '2.11','B' UNION ALL
SELECT '2.2','C' UNION ALL
SELECT '1.5','E' UNION ALL
SELECT '1.3','E'
SELECT t1.* ,
a.*,
b.*,
c.*,
d.*
FROM @Result t1
cross apply dbo.fn_parsenameITVF(Serial,'.',1) a
cross apply dbo.fn_parsenameITVF(Serial,'.',2) b
cross apply dbo.fn_parsenameITVF(Serial,'.',3) c
cross apply dbo.fn_parsenameITVF(Serial,'.',4) d
ORDER BY
a.Item,
b.Item,
c.Item,
d.Item
Lowell
January 6, 2014 at 8:12 am
Lowell (1/3/2014)
Ok Sean, I'm revisiting your ITVF version of this function, and seem to have a problem with it when the data does not have the data at the assumed index;rub my nose in it and tell me what i'm doing wrong.
i renamed your version to fn_parsenameITVF, and here is my test code:
i get no results due to no 4th param found
No nose rubbing at all. You just need to change this to use OUTER APPLY instead of CROSS. Remember that CROSS apply is similar to an INNER join, where the OUTER apply is similar to a LEFT join.
http://qa.sqlservercentral.com/articles/APPLY/69954/[/url]
Here is the working version.
DECLARE @Result TABLE (Serial nvarchar(10),Title varchar(20))
INSERT INTO @Result
SELECT '1.1','a' UNION ALL
SELECT '1.2.1','b' UNION ALL
SELECT '1.2.2','C' UNION ALL
SELECT '1.2.3','G' UNION ALL
SELECT '1.11','B' UNION ALL
SELECT '2.3','B' UNION ALL
SELECT '2.11','B' UNION ALL
SELECT '2.2','C' UNION ALL
SELECT '1.5','E' UNION ALL
SELECT '1.3','E'
SELECT t1.* ,
a.*,
b.*,
c.*,
d.*
FROM @Result t1
outer apply dbo.fn_parsenameITVF(Serial,'.',1) a
outer apply dbo.fn_parsenameITVF(Serial,'.',2) b
outer apply dbo.fn_parsenameITVF(Serial,'.',3) c
outer apply dbo.fn_parsenameITVF(Serial,'.',4) d
ORDER BY
a.Item,
b.Item,
c.Item,
d.Item
_______________________________________________________________
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/
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply