However I posted this one in one of my post.
Querying Microsoft SQL Server : Functions in SQL Server: Functions in SQL Server In SQL Server functions are subrotienes that encapsulate a group of T-SQL statements for reuse.SQL Server pro...
Here I separate ,Split function from that post.
Querying Microsoft SQL Server : Functions in SQL Server: Functions in SQL Server In SQL Server functions are subrotienes that encapsulate a group of T-SQL statements for reuse.SQL Server pro...
Here I separate ,Split function from that post.
CREATE FUNCTION [dbo].[fnSplitString]
(
@string NVARCHAR(MAX),
@delimiter CHAR(1)
)
RETURNS @output TABLE(splitdata NVARCHAR(MAX)
)
BEGIN
DECLARE @start INT, @end INT
SELECT @start = 1, @end = CHARINDEX(@delimiter, @string)
WHILE @start < LEN(@string) + 1 BEGIN
IF @end = 0
SET @end = LEN(@string) + 1
INSERT INTO @output (splitdata)
VALUES(SUBSTRING(@string, @start, @end - @start))
SET @start = @end + 1
SET @end = CHARINDEX(@delimiter, @string, @start)
END
RETURN
END
Exceute this T-sql statements to create function and use as
select *from dbo.fnSplitString('Querying SQL Server','')
Output:
Need some help, If i have a row like this
ReplyDeleteidentity Name Column1 Column2 Column 3
1 test 1,2,3 100,200,300 55,65,75
and i want to split it like
identity id name column1 column2 column3
1 test 1 100 55
1 test 2 200 65
1 test 3 300 75
How to do it?
Hi,
ReplyDeleteHow do i write a query using the split function for the following requirment
I have a table in the following way
Identity Name Col1 Col2 Col3
1 Test1 1,2,3 200,300,400 3.4,6
2 Test2 3,4,5 300,455,600 2,3,8
I want an output in the following format
Identity Name Col1 Col2 Col3
1 Test1 1 200 3
1 Test1 2 300 4
1 Test1 3 400 6
2 Test2 3 300 2
2 Test2 4 455 3
2 Test2 5 600 8
this col1, col2 etc is an identity value from another table and i need ti use joins to get it
Use Cross Apply
Deleteas
SELECT a.name, b.splitdata
FROM dbo.tblsplit a
CROSS APPLY dbo.fnSplitString(a.col1,',') AS b
This comment has been removed by the author.
DeleteThis works like a charm when i have one column to do it, if you see my example i need string split in 3 columns and map the values in the column
ReplyDeleteIdentity Name Col1 Col2 Col3
1 Test1 1,2,3 200,300,400 3.4,6
2 Test2 3,4,5 300,455,600 2,3,8
I want an output in the following format
Identity Name Col1 Col2 Col3
1 Test1 1 200 3
1 Test1 2 300 4
1 Test1 3 400 6
2 Test2 3 300 2
2 Test2 4 455 3
2 Test2 5 600 8
To join based on id of table and comma delimited values
Deleteas
select t.value,k.name,k.col1 from table1 t
inner join
(SELECT a.name, b.splitdata as col1
FROM dbo.tblsplit a
CROSS APPLY dbo.fnSplitString(a.col1,',') AS b) k on k.col1=t.id
and if you want to put this in one table
select k.name,k.col1,k.col2,k.col3 from
(
SELECT a.name, b.splitdata as col1 ,c.splitdata as col2,d.splitdata as col3
FROM dbo.tblsplit a
CROSS APPLY dbo.fnSplitString(a.col1,',') AS b
CROSS APPLY dbo.fnSplitString(a.col2,',') AS c
CROSS APPLy dbo.fnSplitString(a.col3,',') AS d
)k
How to split this
ReplyDeleteselect *from dbo.fnSplitString( 'Quer(,)ying,SQL,Server' , ',' )
I want 3 rows
Quer(,)ying
SQL
Server
For this you need to change function definition , or encode decode your input string and results after splitting.
DeleteFor EX:
CREATE FUNCTION [dbo].[fnSplitString]
(
@string NVARCHAR(MAX),
@delimiter CHAR(1)
)
RETURNS @output TABLE(splitdata NVARCHAR(MAX)
)
BEGIN
SET @string=REPLACE(@string,'(,)','$$')
DECLARE @start INT, @end INT
SELECT @start = 1, @end = CHARINDEX(@delimiter, @string)
WHILE @start < LEN(@string) + 1 BEGIN
IF @end = 0
SET @end = LEN(@string) + 1
INSERT INTO @output (splitdata)
VALUES(REPLACE(SUBSTRING(@string, @start, @end - @start),'$$','(,)'))
SET @start = @end + 1
SET @end = CHARINDEX(@delimiter, @string, @start)
END
RETURN
END
select *from dbo.fnSplitString( 'Quer(,)ying,SQL,Server' , ',' )
returns expected result.
Hey Uma,
ReplyDeleteI just came across your post while searching google, and I want to thank you because it has been very helpful and has solved half of my headaches.I was wondering if you can help me expand it further...
How do I use your SplitStrin function for splitting column/fields with multiple values, instead of taking just a raw string as input, I want it to take a column as input and go through the whole column and split the fields that have multiple values in them into rows. Example
SELECT * FROM dbo.fnSplitStrings (dbo.JobFunction, '#')
I have a table that has:
ID | Fname | Lname | ....other fields...... | JobFunction | ......etc.
12 | John | Doe |....other fields........| 102#103#105 | .......etc.
I want the output to be:
ID | Fname | Lname | ....other fields...... | JobFunction | ......etc.
12 | John | Doe |....other fields........| 102 | .......etc.
12 | null | null |....other fields........| 103 | .......etc.
12 | null | null |....other fields........| 105 | .......etc.
Try this I think it will help
ReplyDeleteSELECT a.ID , a.Fname , a.Lname, b.splitdata
FROM dbo.TableName a
CROSS APPLY dbo.fnSplitString(a.col1,',') AS b
I have 30 items inside a sql database, when it is called by $feat_list on the web page all 30 items are displayed in one loooong column, how do I split this so it is placed inside 2 columns on the web page, tried css but cant get it right
ReplyDeleteif you are using php,you can use script as
Delete$i = 0;
echo "<tr>";
while($row = mysql_fetch_array($results))
{
if ($i % 25 == 0) echo "</tr><tr>";
echo "<td>".$row['Destination']."</td>";
$i++;
}
echo "</tr>";
http://stackoverflow.com/questions/18013970/split-table-rows-into-columns
How to split below using SQL server 2008
ReplyDelete-- Name: Split_String
-- Desc: This function splits a string into 3 columns based on a delimiter, while being limited by maximum length allowed in each column
-- Input Parameters:
-- p_string (mandatory) - string to be split
-- p_delimiter (optional, defaulted to comma) - the delimiter to be used to split
-- p_limit (optional, defaulted to 45) - maximum length of each component
-- p_ind (optional, defaulted to return all 3 columns separated by pipes) - indicates which column to be sent as result i.e column 1 or 2 or 3
create or replace function split_string
( p_string in varchar2
, p_delimiter in varchar2 := ','
, p_limit in number := 45
, p_ind in number := 0
)
return varchar2 is
-- Defining local variables and their default values
v_split1 number;
v_split2 number;
v_column1 varchar2(1000) := NULL;
v_column2 varchar2(1000) := NULL;
v_column3 varchar2(1000) := NULL;
v_return varchar2(1000) := NULL;
v_output_delimiter char(1) := '|';
begin
v_column1 := trim(p_string);
-- Split only if length is more than allowed length
if (length(v_column1) > p_limit) then
-- Find out the LAST delimiter position with the allowed length - thereby taking as much data as possible in the first column.
v_split1 := instr(substr(v_column1,1,p_limit+1),p_delimiter,-1);
-- Continue only if there is a delimiter found within allowed length of characters
if (v_split1 <> 0) then
-- Split the string into two columns. First column is frozen from now on. Operate only on second column.
-- IMP: Do not change the order. Column 2 is used first, as Column 1 is overwritten in the next command.
v_column2 := trim(substr(v_column1,v_split1+1));
v_column1 := trim(substr(v_column1,1,v_split1-1));
-- Split only if column 2 length is more than allowed length
if (length(v_column2) > p_limit) then
-- Find out the LAST delimiter position with the allowed length - thereby taking as much data as possible in the second column.
v_split2 := instr(trim(substr(v_column2,1,p_limit+1)),p_delimiter,-1);
-- Continue only if there is a delimiter found within allowed length of characters
if (v_split2 <> 0) then
-- Split the column 2 further into two columns.
-- IMP: Do not change the order. Column 3 is used first, as Column 2 is overwritten in the next command.
v_column3 := trim(substr(v_column2,v_split2+1));
v_column2 := trim(substr(v_column2,1,v_split2-1));
end if;
end if;
end if;
end if;
-- Return column 1/2/3 based on the output indicator
-- If the value is not 1/2/3, then return full string delimited by pipe.
case p_ind
when 1 then
v_return := v_column1;
when 2 then
v_return := v_column2;
when 3 then
v_return := v_column3;
else
v_return := v_column1 || v_output_delimiter || v_column2 || v_output_delimiter || v_column3;
end case;
return v_return;
end;