November 21, 2016 at 7:10 am
I have below kind of strings:
21-12-ABCD
23-1-hdf
19-1345-dsnf
i want a result like below for each string:
21 12
23 1
19 1345
How can i extract numbers before and after each hyphen?
November 21, 2016 at 7:22 am
Find the article with the string splitter named DelimitedSplit8K here on SSC and download it;
it makes spitting things like this a lot easier.
once split, you just filter for items that are numeric
/*
--Results
val ItemNumber Item
21-12-ABCD 1 21
21-12-ABCD 2 12
23-1-hdf 1 23
23-1-hdf 2 1
19-1345-dsnf 1 19
19-1345-dsnf 2 1345
*/
;WITH MyCTE([val])
AS
(
SELECT '21-12-ABCD' UNION ALL
SELECT '23-1-hdf' UNION ALL
SELECT '19-1345-dsnf'
)
SELECT * FROM MyCTE
CROSS APPLY master.dbo.DelimitedSplit8K([val],'-') fn
WHERE ISNUMERIC(fn.Item) = 1
Lowell
November 21, 2016 at 7:35 am
i want to write a tsql query for it.
November 21, 2016 at 7:40 am
sqlinterset (11/21/2016)
i want to write a tsql query for it.
DelimitedSplit8K is a TSQL function.
Lowell
November 21, 2016 at 8:37 am
i dont have it in my sql library .. is there any other way using traditional sql?
November 21, 2016 at 8:41 am
sqlinterset (11/21/2016)
i dont have it in my sql library .. is there any other way using traditional sql?
without reinventing the wheel?
it's much easier to add it:
it's in the zip files at the bottom of this article:
Tally OH! An Improved SQL 8K “CSV Splitter” Function[/url]
Lowell
November 21, 2016 at 8:47 am
If your string pattern is consistent and you are 100% certain you will never have any decimal points you can do something like this.
Thanks to Lowell for turning this into something so easily consumable.
WITH MyCTE([val])
AS
(
SELECT '21-12-ABCD' UNION ALL
SELECT '23-1-hdf' UNION ALL
SELECT '19-1345-dsnf'
)
select parsename(replace(val, '-', '.'), 3)
, parsename(replace(val, '-', '.'), 2)
from MyCTE
_______________________________________________________________
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/
November 21, 2016 at 9:27 am
Another way:
WITH MyCTE(val) AS
(
SELECT '21-12-ABCD' UNION ALL
SELECT '23-1-hdf' UNION ALL
SELECT '19-1345-dsnf'
)
SELECT val,
SUBSTRING(val, 1, d1.d-1),
SUBSTRING(val, d1.d+1, CHARINDEX('-',val, d1.d+1)-(d1.d+1))
FROM MyCTE
CROSS APPLY (VALUES (CHARINDEX('-',val))) d1(d);
-- Itzik Ben-Gan 2001
November 28, 2016 at 9:23 am
Just for the fun of it...
Here's a function that'll remove all non-numeric values and place a single space between non-contiguous groups of numbers (not dependent on any specific pattern or delimiters)
CREATE FUNCTION tfn_NumbersOnlyWithGaps
/* ================================================================================
11/28/2016 JL, Created...
Inline table valued function. Removes all non-numeric characters and adds a space between number blocks
================================================================================ */
(
@String VARCHAR(8000)
)
RETURNS TABLE WITH SCHEMABINDING AS
RETURN
WITH
n (n) AS (SELECT 1 FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) n (n)),
cte_Tally (n) AS (
SELECT TOP (LEN(@String))
ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM n n1, n n2, n n3, n n4
),
cte_GetNums AS (
SELECT
t.n,
Num = CASE WHEN ASCII(SUBSTRING(@String, t.n, 1)) BETWEEN 48 AND 57 THEN SUBSTRING(@String, t.n, 1) END
FROM
cte_Tally t
),
cte_FixSpaces AS (
SELECT
gn.n,
Num = CASE WHEN gn.Num IS NULL AND LEAD(gn.Num, 1) OVER(ORDER BY gn.n) IS NOT NULL THEN ' ' ELSE gn.Num END
FROM
cte_GetNums gn
)
SELECT NumString = (
SELECT
fs.Num
FROM
cte_FixSpaces fs
WHERE
fs.Num IS NOT NULL
ORDER BY
fs.n
FOR XML PATH(''), TYPE).value('.', 'nvarchar(max)'
);
GO
Some test data...
IF OBJECT_ID('tempdb..#TestData', 'U') IS NOT NULL
DROP TABLE #TestData;
CREATE TABLE #TestData (
Value VARCHAR(50)
);
INSERT #TestData (Value) VALUES
('21-12-ABCD'),('23-1-hdf'),('19-1345-dsnf'),
('21-12-ABCD654'),('23-1-hdf967'),('19-1345-dsnf258'),
('1q2w3e4r5t6y'),('123edc4567ygv'),('369jhg852hjk147');
SELECT
td.Value,
nwg.NumString
FROM
#TestData td
CROSS APPLY tfn_NumbersOnlyWithGaps(td.Value) nwg;
Results
Value NumString
------------------- ----------------------------------------------
23-1-hdf 23 1
19-1345-dsnf 19 1345
21-12-ABCD654 21 12 654
23-1-hdf967 23 1 967
19-1345-dsnf258 19 1345 258
1q2w3e4r5t6y 1 2 3 4 5 6
123edc4567ygv 123 4567
369jhg852hjk147 369 852 147
November 28, 2016 at 10:04 am
Assuming Jason's interpretation of the OP's requirement is correct and the requirement was to have all the numbers in a single column separated by spaces (which looks correct but who knows considering how the OP has disappeared)...
You could use patternsplitCM[/url] like this:
SELECT NumbersOnlyWithGaps =
(
SELECT item + ' '
FROM dbo.patternsplitCM('123-444-rrrrttt','%[0-9]%')
WHERE matched = 1
FOR XML PATH('')
);
Against a table it would look like this:
-- sample data
DECLARE @sample TABLE (string varchar(100));
INSERT @sample VALUES ('123-22-xxx'), ('33-22-44-ff'), ('xxx-3-sss-456');
-- solution
SELECT *
FROM @sample
CROSS APPLY
(
SELECT NumbersOnlyWithGaps =
(
SELECT item + ' '
FROM dbo.patternsplitCM(string,'%[0-9]%')
WHERE matched = 1
FOR XML PATH('')
)
) x;
-- Itzik Ben-Gan 2001
November 28, 2016 at 10:29 am
sqlinterset (11/21/2016)
i dont have it in my sql library ..
You should add it. It comes in real handy. But, since you're using 2016, you should be able to use the new STRING_SPLIT function.
https://msdn.microsoft.com/en-us/library/mt684588.aspx
--Jeff Moden
November 28, 2016 at 12:08 pm
Alan.B (11/28/2016)
Assuming Jason's interpretation of the OP's requirement is correct and the requirement was to have all the numbers in a single column separated by spaces (which looks correct but who knows considering how the OP has disappeared)...You could use patternsplitCM[/url] like this:
SELECT NumbersOnlyWithGaps =
(
SELECT item + ' '
FROM dbo.patternsplitCM('123-444-rrrrttt','%[0-9]%')
WHERE matched = 1
FOR XML PATH('')
);
Against a table it would look like this:
-- sample data
DECLARE @sample TABLE (string varchar(100));
INSERT @sample VALUES ('123-22-xxx'), ('33-22-44-ff'), ('xxx-3-sss-456');
-- solution
SELECT *
FROM @sample
CROSS APPLY
(
SELECT NumbersOnlyWithGaps =
(
SELECT item + ' '
FROM dbo.patternsplitCM(string,'%[0-9]%')
WHERE matched = 1
FOR XML PATH('')
)
) x;
If the sample data provided by the OP is what all of the looks like, there is no need to go to the expense of splitting the strings at all.
The following would be much faster than the fasted splitter function...
SELECT
td.Value,
NewValue = LEFT(x2.Value, x3.c2)
FROM
#TestData td
CROSS APPLY (SELECT CHARINDEX('-', td.Value)) x1 (c1)
CROSS APPLY (SELECT STUFF(td.Value, x1.c1, 1, ' ')) x2 (Value)
CROSS APPLY (SELECT CHARINDEX('-', x2.Value) -1) x3 (c2);
Of that only works with the "numbers dash numbers dash letters" formatting.
Plus it boring... My "just for the fun of it" solution answered (what I thought) was a more interesting question.
November 28, 2016 at 1:31 pm
Of that only works with the "numbers dash numbers dash letters" formatting.
Plus it boring... My "just for the fun of it" solution answered (what I thought) was a more interesting question.
Yep, Yep - you answered the more interesting question for sure! 😉
Working with strings is so much fun!
-- Itzik Ben-Gan 2001
November 28, 2016 at 2:38 pm
Alan.B (11/28/2016)
Of that only works with the "numbers dash numbers dash letters" formatting.
Plus it boring... My "just for the fun of it" solution answered (what I thought) was a more interesting question.
Yep, Yep - you answered the more interesting question for sure! 😉
Working with strings is so much fun!
Agreed... Tricking SQL Server into being good at things it's not good at, is always a fun challenge.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply