June 19, 2014 at 7:25 am
Hello comunity
i need to cut my string on 3 portion, for exemple my string is :
a) 1.9999-Q1
b) 01.9999-Q11
I need to keep all values before "." (point), and all values after "-" , and also all values between "." and "-", like this:
a) 1
9999
Q1
b) 01
9999
Q11
Someone could give me some help.
Many thanks
Luis Santos
June 19, 2014 at 7:44 am
one of the manyways:
;WITH sample_data(val)
AS
(
SELECT '1.9999-Q1'
UNION SELECT '01.9999-Q11'
)
SELECT *, PARSENAME(x.namelike,3) Part1,PARSENAME(x.namelike,2) Part2,PARSENAME(x.namelike,1) Part3
FROM sample_data
CROSS APPLY (SELECT REPLACE(val,'-','.')) x(namelike)
June 19, 2014 at 7:56 am
Another of many
😎
DECLARE @STR_A VARCHAR(50) = '1.9999-Q1'
;WITH STR_POS AS
(
SELECT
CHARINDEX(CHAR(46),@STR_A) AS P1
,CHARINDEX(CHAR(45),@STR_A) AS P2
,LEN(@STR_A) AS P3
)
SELECT
SUBSTRING(@STR_A,1,SP.P1 - 1) AS A
,SUBSTRING(@STR_A,SP.P1 + 1,(SP.P2 - SP.P1) - 1) AS B
,SUBSTRING(@STR_A,SP.P2 + 1,(SP.P3 - SP.P2)) AS C
FROM STR_POS SP
June 19, 2014 at 8:04 am
Hello Eugene
work good, if i want to try like this without using FROM TABLE clause or CTE:
DECLARE @ref AS CHAR(18)
SET @ref = '8982214567.9999-Q199999999'
do you please send my how changes to apply.
Select .....etc
Best regards
Luis Santos
June 19, 2014 at 8:15 am
I am very suspicient that you doing something wrong there....
But anyway, here is the answer:
DECLARE @ref AS CHAR(18)
SET @ref = REPLACE('8982214567.9999-Q199999999','-','.')
SELECT PARSENAME(@ref ,3) Part1,PARSENAME(@ref ,2) Part2,PARSENAME(@ref ,1) Part3
June 19, 2014 at 8:22 am
Many thanks for all of you
Best regards
Luis Santos
June 19, 2014 at 9:37 am
Without using parsename:
;WITH sample_data(val)
AS
(
SELECT '1.9999-Q1'
UNION SELECT '01.9999-Q11'
)
SELECTval,
part1 = substring(val,1,charindex('.',val)-1),
part2 = substring(val,charindex('.',val)+1,(charindex('-',val)-charindex('.',val))-1),
part3 = substring(val,charindex('-',val)+1, len(val))
FROM sample_data
... and a couple ways to do this directly from a variable:
DECLARE @ref AS CHAR(18)
SET @ref = '8982214567.9999-Q199999999';
SELECT@ref,
part1 = substring(@ref,1,charindex('.',@ref)-1),
part2 = substring(@ref,charindex('.',@ref)+1,(charindex('-',@ref)-charindex('.',@ref))-1),
part3 = substring(@ref,charindex('-',@ref)+1, len(@ref))
SELECT@ref,
part1 = substring(c,1,charindex('.',c)-1),
part2 = substring(c,charindex('.',c)+1,(charindex('-',c)-charindex('.',c))-1),
part3 = substring(c,charindex('-',c)+1, len(c))
FROM (VALUES (@ref)) t(c)
Edit: fixed code formatting; added 2nd example
-- Itzik Ben-Gan 2001
June 19, 2014 at 9:53 am
May I ask please, if there is any reason to use SUBSTRING instead of PARSENAME other than love of typing and having more of pink in the code, especially for case with a single variable?
June 19, 2014 at 10:12 am
Eugene Elutin (6/19/2014)
May I ask please, if there is any reason to use SUBSTRING instead of PARSENAME other than love of typing and having more of pink in the code, especially for case with a single variable?
No reason I had a few minutes to kill and wanted to show another way to do it.
SUBSTRING is more standard than PARSENAME so the code would be more portable using SUBSTRING (for those people that care about that stuff :-P)
Here's a way to do it with less pink:
;WITH sample_data(val)
AS
(
SELECT '1.9999-Q1'
UNION SELECT '01.9999-Q11'
)
SELECTval,
part1=max(case when ItemNumber = 1 then item end),
part2=max(case when ItemNumber = 2 then item end),
part3=max(case when ItemNumber = 3 then item end)
FROM sample_data sd
CROSS APPLY dbo.DelimitedSplit8K(replace(sd.val,'-','.'),'.') x
GROUP BY val;
-- Itzik Ben-Gan 2001
June 19, 2014 at 10:30 am
And a different way of doing this with the Pattern Splitter.
http://www.sqlservercentral.com/articles/String+Manipulation/94365/
WITH sample_data(val)
AS
(
SELECT '1.9999-Q1'
UNION SELECT '01.9999-Q11'
)
SELECTval,
part1=max(case when ItemNumber = 1 then item end),
part2=max(case when ItemNumber = 3 then item end),
part3=max(case when ItemNumber = 5 then item end)
FROM sample_data sd
CROSS APPLY dbo.PatternSplitCM(sd.val, '%[-.]%') x
WHERE Matched = 0
GROUP BY val;
June 19, 2014 at 11:13 am
Yet two more ways
😎
USE tempdb;
GO
DECLARE @TSTR TABLE
(
TSTR_ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED NOT NULL
,TSTR_VALUE VARCHAR(50) NOT NULL
);
INSERT INTO @TSTR (TSTR_VALUE)
VALUES
('11.9999-Q101')
,('12.9999-Q102')
,('13.9999-Q103')
,('14.9999-Q104')
,('15.9999-Q105')
,('16.9999-Q106')
,('17.9999-Q107')
,('18.9999-Q108')
,('19.9999-Q109')
,('10.9999-Q1010');
DECLARE @DELIM_1 CHAR(1) = '.';
DECLARE @DELIM_2 CHAR(1) = '-';
SELECT
TS.TSTR_ID
,SUBSTRING(TS.TSTR_VALUE,1,X.D1 -1)
,SUBSTRING(TS.TSTR_VALUE,X.D1 + 1, (X.D2 - X.D1) - 1)
,SUBSTRING(TS.TSTR_VALUE,X.D2 + 1,8000)
FROM @TSTR TS
OUTER APPLY
(
SELECT
CHARINDEX(@DELIM_1,TS.TSTR_VALUE) D1
,CHARINDEX(@DELIM_2,TS.TSTR_VALUE) D2
) AS X
USE tempdb;
GO
DECLARE @TSTR TABLE
(
TSTR_ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED NOT NULL
,TSTR_VALUE VARCHAR(50) NOT NULL
);
INSERT INTO @TSTR (TSTR_VALUE)
VALUES
('11.9999-Q101')
,('12.9999-Q102')
,('13.9999-Q103')
,('14.9999-Q104')
,('15.9999-Q105')
,('16.9999-Q106')
,('17.9999-Q107')
,('18.9999-Q108')
,('19.9999-Q109')
,('10.9999-Q1010');
DECLARE @SQL_STR NVARCHAR(MAX) = N'
DECLARE @TSX TABLE
(
TX_ID INT NOT NULL
,TX_V1 VARCHAR(50) NOT NULL
,TX_V2 VARCHAR(50) NOT NULL
,TX_V3 VARCHAR(50) NOT NULL
);
INSERT INTO @TSX (TX_ID,TX_V1,TX_V2,TX_V3)
VALUES ' +
(SELECT
STUFF((
SELECT
NCHAR(44) + NCHAR(40) + NCHAR(39) + CAST(TS.TSTR_ID AS NVARCHAR(12)) + NCHAR(39) + NCHAR(44) + NCHAR(39) +
REPLACE(REPLACE(TS.TSTR_VALUE,N'.',N''','''),N'-',N''',''')
+ CHAR(39) + NCHAR(41)
FROM @TSTR TS
FOR XML PATH(''), TYPE).value('.[1]','NVARCHAR(MAX)'),1,1,'')) + N';
SELECT * FROM @TSX';
PRINT @SQL_STR
EXEC (@SQL_STR);
June 19, 2014 at 11:23 am
SUBSTRING is more standard than PARSENAME so the code would be more portable using SUBSTRING (for those people that care about that stuff :-P)
Yep, as long you port to some RDBS which also has more standard SUBSTRING function (btw some don't eg. Oracle).
DelimitedSplit8K is great ITV function but it is more intended for use when values to split may contain variable number of parts.
PatternSplitCM is not as good as DelimitedSplit8K from performance prospective (as it is loop based).
Both are definitely over-kill for a SINGLE variable value.
However, looks like the competition for "The most obscure way to do simple thing" is open, I would like to add my method, especially designed for solving OP issue of splitting single value from local variable (most likely input parameter of stored proc.):
DECLARE @ref AS CHAR(18)
SET @ref = '8982214567.9999-Q199999999'
;WITH SimpleCalculations
AS
(
SELECT Original.Value
,Parts.PartNo
,CASE Parts.PartNo WHEN 1 THEN 0 WHEN 2 THEN MIN(SymbolPos) + 1 ELSE MAX(SymbolPos) + 1 END StartPos
,CASE Parts.PartNo WHEN 1 THEN MIN(SymbolPos) WHEN 2 THEN MAX(SymbolPos) ELSE LEN(Original.Value) + 1 END EndPos
FROM (VALUES (@ref)) Original(Value)
CROSS JOIN (VALUES (1), (2), (3)) Parts(PartNo)
CROSS JOIN (VALUES ('.'), ('-')) Separators(Symbol)
CROSS APPLY (SELECT CHARINDEX(Separators.Symbol, Original.Value)) P(SymbolPos)
GROUP BY Original.Value, Parts.PartNo
)
SELECT Value , [1] Part1, [2] Part2, [3] Part3
FROM (SELECT Value, PartNo, SUBSTRING(Value, StartPos, EndPos - StartPos) Part
FROM SimpleCalculations) AS PivotSource
PIVOT (MAX(Part) FOR PartNo IN ([1], [2], [3]) ) AS PivotTable;
P.S. I also thought to use FOR XML as previous poster, but I went for PIVOT as more powerful and more standard (ANSI) command.
:hehe:
June 19, 2014 at 12:14 pm
Eugene Elutin (6/19/2014)
SUBSTRING is more standard than PARSENAME so the code would be more portable using SUBSTRING (for those people that care about that stuff :-P)
Yep, as long you port to some RDBS which also has more standard SUBSTRING function (btw some don't eg. Oracle).
Oracle does have SUBSTRING but it works a little different. Everyone has substring or something similar (e.g. I could have used LEFT, which is available in nearly all RDBMS'.
DelimitedSplit8K is great ITV function but it is more intended for use when values to split may contain variable number of parts.
It still works nicely - and achieved the objective of using less code. I would never use it for something like the OP because it is overkill; I was just showing alternative methods.
PatternSplitCM is not as good as DelimitedSplit8K from performance prospective (as it is loop based).
Yes - DelimitedSplit8K would be faster. Regarding (as [patternSplitCM] is loop based): I think you are mistaken or are looking at a different version of PatternSplitCM than what I am using[/url]. No loop here:
-- Created by: Chris Morris 12-Oct-2012
ALTER FUNCTION [dbo].[PatternSplitCM]
(
@List VARCHAR(8000) = NULL
,@Pattern VARCHAR(50)
) RETURNS TABLE WITH SCHEMABINDING
AS
RETURN
WITH numbers AS (
SELECT TOP(ISNULL(DATALENGTH(@List), 0))
n = ROW_NUMBER() OVER(ORDER BY (SELECT NULL))
FROM
(VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) d (n),
(VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) e (n),
(VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) f (n),
(VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) g (n))
SELECT
ItemNumber = ROW_NUMBER() OVER(ORDER BY MIN(n)),
Item = SUBSTRING(@List,MIN(n),1+MAX(n)-MIN(n)),
[Matched]
FROM (
SELECT n, y.[Matched], Grouper = n - ROW_NUMBER() OVER(ORDER BY y.[Matched],n)
FROM numbers
CROSS APPLY (
SELECT [Matched] = CASE WHEN SUBSTRING(@List,n,1) LIKE @Pattern THEN 1 ELSE 0 END
) y
) d
GROUP BY [Matched], Grouper
-- Itzik Ben-Gan 2001
June 20, 2014 at 3:57 am
Alan.B (6/19/2014)
Eugene Elutin (6/19/2014)
SUBSTRING is more standard than PARSENAME so the code would be more portable using SUBSTRING (for those people that care about that stuff :-P)
Yep, as long you port to some RDBS which also has more standard SUBSTRING function (btw some don't eg. Oracle).
Oracle does have SUBSTRING but it works a little different. Everyone has substring or something similar (e.g. I could have used LEFT, which is available in nearly all RDBMS'.
Oracle does not have SUBSTRING function! Your link does refer to MySql sytax. In Oracle you need to use SUBSTR, so it's looks almost the same, but different...
June 20, 2014 at 9:12 am
Eugene Elutin (6/20/2014)
Alan.B (6/19/2014)
Eugene Elutin (6/19/2014)
SUBSTRING is more standard than PARSENAME so the code would be more portable using SUBSTRING (for those people that care about that stuff :-P)
Yep, as long you port to some RDBS which also has more standard SUBSTRING function (btw some don't eg. Oracle).
Oracle does have SUBSTRING but it works a little different. Everyone has substring or something similar (e.g. I could have used LEFT, which is available in nearly all RDBMS'.
Oracle does not have SUBSTRING function! Your link does refer to MySql sytax. In Oracle you need to use SUBSTR, so it's looks almost the same, but different...
True that. My bad. :blush:
-- Itzik Ben-Gan 2001
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply