September 7, 2008 at 11:25 pm
I got a table with values
fld_batchno fld_qty
12 10+12+15
15 10+20+30
20 10+15+16+14+15
I want to extract the values from table as
10
12
15
.................so on
want to get it into datatable in .net for reporting part
can anyone help??????????/
September 8, 2008 at 2:05 am
Hi, Use the following code to assist;
==============================================================================
CREATE FUNCTION [SCHEMA].[NAME]
(
@myDelimitedList NVARCHAR(MAX),
@mySeparator NVARCHAR(255)
)
RETURNS @tblList TABLE (vchItem NVARCHAR(MAX))
AS
BEGIN
DECLARE @mySeparatorPosition INT;
DECLARE @myExtractedValue NVARCHAR(255);
WHILE LTRIM(@myDelimitedList) <> N''
/* WHILE BATCH */
BEGIN
/* GET POSITION OF FIRST SEPARATOR */
SELECT @mySeparatorPosition = (SELECT CHARINDEX(@mySeparator,@myDelimitedList))
IF @mySeparatorPosition = 0
/* RETURN THE WHOLE LIST IF NO SEPARATOR FOUND AND INSERT INTO TABLE */
BEGIN
SELECT @myExtractedValue = LTRIM(@myDelimitedList)
INSERT INTO @tblList VALUES(@myExtractedValue)
BREAK
END
/* SET FIRST ITEM LIST */
SELECT @myExtractedValue = LEFT(@myDelimitedList,@mySeparatorPosition-1)
/* CHANGE THE INPUT PARAMETER TO REMOVE THE FIRST LIST ITEM */
SELECT @myDelimitedList = LTRIM(SUBSTRING(@myDelimitedList,@mySeparatorPosition+1,99999))
/* CHECK EXTRACTED VALUE */
IF COALESCE(@myExtractedValue,N'') = N''
CONTINUE
/* INSERT INTO TABLE IF NOT EMPTY */
ELSE
INSERT INTO @tblList VALUES(@myExtractedValue)
END
RETURN
END
==============================================================================
Then use a SP with the following code;
==============================================================================
DECLARE @myList NVARCHAR(MAX)
SELECT @myList = N'John,Paul,George,Ringo'
DECLARE @mySeparator NVARCHAR(1)
SELECT @mySeparator = N','
SELECT * FROM CORE.funcGetTableFromDelimitedList (@myList,@mySeparator)
==============================================================================
You may need to tweak but does work well!!:D
September 8, 2008 at 8:33 am
This function will very rapidly parse out lists like that into columns.
ALTER function [dbo].[StringParserXML]
(@String_in varchar(max),
@Delimiter_in char(10))
returns @Parsed table (
Row int,
Parsed varchar(100))
as
begin
if right(@string_in, 1) = @delimiter_in
select @string_in = left(@string_in, len(@string_in) - len(@delimiter_in))
declare @XML xml
select @xml = ' '
insert into @parsed(row, parsed)
select row_number() over (order by x.i.value('.', 'varchar(100)')), x.i.value('.', 'varchar(100)')
from @xml.nodes('//i') x(i)
return
end;
For a situation like yours, you'll want to use Cross Apply:
select fld_batchno, parsed
from dbo.YourTable
cross apply dbo.StringParserXML(fld_qty, '+');
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply