August 29, 2012 at 7:19 am
I have a table like this-
Sno Text
1 Batch 58701, Sub -000 .
2 Batch 16260, Sub 000 AND Batch 16264, Sub 000.
3 Batch 41723 001 through 011 013 through 017 901 through 904 53640 001 through 011 013 through 017
4 Batch and Sub : 87028/000 87600/000/210 87037/000 87601/000/210 87039/000 87602/000/210 87070/000 87603/000/210 87071/000 87604/000/210
I need to extract all Batches(5 Digit Number) and Subs(3 Digit Numbers) and place in 2 columns. My output should look like this-
Sno Batch Sub
1 8701 000
2 16260 000
2 16264 000
3 41723 001
3 41723 013
.
.
.
Can Anyone have idea about this..
Thanks in Advance!
Kumar
August 29, 2012 at 7:55 am
Kumar SQL (8/29/2012)
I have a table like this-Sno Text
1 Batch 58701, Sub -000 .
2 Batch 16260, Sub 000 AND Batch 16264, Sub 000.
3 Batch 41723 001 through 011 013 through 017 901 through 904 53640 001 through 011 013 through 017
4 Batch and Sub : 87028/000 87600/000/210 87037/000 87601/000/210 87039/000 87602/000/210 87070/000 87603/000/210 87071/000 87604/000/210
I need to extract all Batches(5 Digit Number) and Subs(3 Digit Numbers) and place in 2 columns. My output should look like this-
Sno Batch Sub
1 8701 000
2 16260 000
2 16264 000
3 41723 001
3 41723 013
.
.
.
Can Anyone have idea about this..
Thanks in Advance!
Kumar
Why your data looks to be horribly not normalized.
Can you post ddl (create table), sample data (insert statements) and desired output based on your sample data? Without the proper ddl and sample data we are just guessing. Take a look at the first link in my signature for best practices when posting questions.
_______________________________________________________________
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/
August 29, 2012 at 11:04 am
Try this. It uses Jeff Moden's 8K splitter - full details on this site...
declare @temp table
(
Sno int, Text1 varchar(200)
);
insert @temp values (1, 'Batch 58701, Sub -000 .');
insert @temp values (2, 'Batch 16260, Sub 000 AND Batch 16264, Sub 000. ');
insert @temp values (3, 'Batch 41723 001 through 011 013 through 017 901 through 904 53640 001 through 011 013 through 017');
insert @temp values (4, 'Batch and Sub : 87028/000 87600/000/210 87037/000 87601/000/210 87039/000 87602/000/210 87070/000 87603/000/210 87071/000 87604/000/210 ');
--select * from @temp;
--I need to extract all Batches(5 Digit Number) and Subs(3 Digit Numbers) and place in 2 columns. My output should look like this-
/*
Sno Batch Sub
1 8701 000
2 16260 000
2 16264 000
3 41723 001
3 41723 013
*/
drop table #data;
create table #data
(
sno int,
ItemNumber int,
Batch Char(5),
Sub Char(3),
primary key (sno, ItemNumber)
);
with cte1 as
(
select sno,
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(text1, 'Batch', ' ')
, ',', ' ')
, '.', ' ')
, ':', ' ')
, 'sub', ' ')
, 'and', ' ')
, '-', ' ')
, '/', ' ')
, 'through', '') as Text1
from @temp
),
cte2 as
(
select sno,
REPLACE(REPLACE(REPLACE(Text1, ' ', ' '), ' ', ' '), ' ', ' ') as Text1
from cte1
),
cte3 as
(
select C.sno, A.*
from cte2 C
cross apply [dbo].[DelimitedSplit8K] ( c.Text1, ' ' ) A
where A.Item <> ' '
),
cte4 as
(
select sno, ItemNumber,
Batch = case when len(Item)=5 then Item else null end,
Sub = case when len(Item)=3 then Item else null end
from cte3
)
insert #data
select *
from cte4
declare @Batch char(5);
update #data
set @Batch = Batch = case when batch is null then @Batch else Batch end
OPTION (MAXDOP 1);
delete #data where Sub is null;
select * from #data;
/*
--===================================================================================
CREATE FUNCTION [dbo].[DelimitedSplit8K]
--===== Define I/O parameters
(@pString VARCHAR(8000), @pDelimiter CHAR(1))
--WARNING!!! DO NOT USE MAX DATA-TYPES HERE! IT WILL KILL PERFORMANCE!
RETURNS TABLE WITH SCHEMABINDING AS
RETURN
--===== "Inline" CTE Driven "Tally Table" produces values from 1 up to 10,000...
-- enough to cover VARCHAR(8000)
WITH E1(N) AS (
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
), --10E+1 or 10 rows
E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows
E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max
cteTally(N) AS (--==== This provides the "base" CTE and limits the number of rows right up front
-- for both a performance gain and prevention of accidental "overruns"
SELECT TOP (ISNULL(DATALENGTH(@pString),0)) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4
),
cteStart(N1) AS (--==== This returns N+1 (starting position of each "element" just once for each delimiter)
SELECT 1 UNION ALL
SELECT t.N+1 FROM cteTally t WHERE SUBSTRING(@pString,t.N,1) = @pDelimiter
),
cteLen(N1,L1) AS(--==== Return start and length (for use in substring)
SELECT s.N1,
ISNULL(NULLIF(CHARINDEX(@pDelimiter,@pString,s.N1),0)-s.N1,8000)
FROM cteStart s
)
--===== Do the actual split. The ISNULL/NULLIF combo handles the length for the final element when no delimiter is found.
SELECT ItemNumber = ROW_NUMBER() OVER(ORDER BY l.N1),
Item = SUBSTRING(@pString, l.N1, l.L1)
FROM cteLen l
;
*/
August 29, 2012 at 8:03 pm
Very messy, but kinda fun!
;WITH Tally (n) AS (
SELECT TOP 200 ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM sys.all_columns),
Chars AS (
SELECT Sno, n, char1
FROM @temp
CROSS APPLY (SELECT text2=REPLACE(Text1, '/', ' ')) a
CROSS APPLY (
SELECT n, char1=CASE WHEN PATINDEX('%[^0-9 ]%', SUBSTRING(text2, n, 1)) > 0
THEN NULL ELSE SUBSTRING(text2, n, 1) END
FROM Tally
WHERE n BETWEEN 1 AND LEN(Text2)) b),
Groups AS (
SELECT Sno, CleanedText=
LTRIM(RTRIM((SELECT '' + char1
FROM Chars a
WHERE a.Sno = b.Sno
FOR XML PATH(''), TYPE).value('.', 'VARCHAR(MAX)')))
FROM Chars b
GROUP BY Sno),
Items AS (
SELECT Sno, ItemNumber, Item
FROM Groups
CROSS APPLY dbo.DelimitedSplit8K(CleanedText, ' ')
WHERE Item <> ' ')
SELECT Sno, Batch=(
SELECT TOP 1 item
FROM Items b
WHERE a.Sno=b.Sno AND b.ItemNumber < a.ItemNumber AND LEN(Item) = 5
ORDER BY ItemNumber DESC), Sub=Item
FROM Items a
WHERE LEN(Item) = 3
Uses Laurie's setup data and also the same DelimitedSplit8K FUNCTION she provided, just a slightly different approach.
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
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply