SSMS COLUMN SPLIT QUERY - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

SSMS COLUMN SPLIT QUERY

Author

Topic

kond.mohan
Posting Yak Master

213 Posts

Posted - 2012-03-09 : 04:48:05

HI,

I HAVE USED SQL QUERY

ONE OF MY FIELD EXISTED LIKE

ABC|(PIPE SYMBOL)CDE|EFG
SDF|SDF|DFS|SFD|SFD|ERW|
QWE|tTY|

WE NEED TO SPLIT THE FIRST COLUMN ROW LIKE THIS WAY

abc,
cde,
efg

how can i get data this way

can anybody know the logic help me

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-03-09 : 05:40:32

[code]-- First we need a function to split the values
if object_id('fnParseArray') is not null
begin
drop function fnParseArray
end
go
CREATE FUNCTION dbo.fnParseArray (@array VARCHAR(max),@separator CHAR(1))
RETURNS @T Table (occ int,col1 varchar(50))
AS
BEGIN
DECLARE @separator_position INT
DECLARE @array_value VARCHAR(max)
declare @occurence int =1
if (left(@array,1)=@separator)
begin
set @array=stuff(@array,1,1,'')
end
if (right(@array,1)<>@separator)
begin
SET @array = @array + @separator
end
-- Loop through the string searching for separtor characters
WHILE PATINDEX('%' + @separator + '%', @array) <> 0
BEGIN
SELECT @separator_position = PATINDEX('%' + @separator + '%',@array)
SELECT @array_value = LEFT(@array, @separator_position - 1)
INSERT into @T VALUES (@occurence,@array_value)
set @occurence = @occurence + 1
SELECT @array = STUFF(@array, 1, @separator_position, '')
END
RETURN
END
go

-- Now we need your sample data in a table variable to have a test
declare @yak table(id int identity(1,1), testCol varchar(255))
insert @yak
select 'ABC|CDE|EFG' union all
select 'SDF|SDF|DFS|SFD|SFD|ERW|' union all
select 'QWE|tTY|'

-- show sample data
select * from @yak

-- the solution
select y.id,t2.col1
from @yak y outer apply dbo.fnParseArray(testCol,'|') t2
[/code]

No, you're never too old to Yak'n'Roll if you're too young to die.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-09 : 11:52:55

http://visakhm.blogspot.com/2010/02/parsing-delimited-string.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources