splitting one column into many

rakeshs312

Hi, I have one column in one table in sql server like SUK ----- abc 111 bbb 1212 pqr 222 ccc 2323 i have to split this in to 4 columns how to do this,,,please help DOnt update the table ,only for viewing. in a storedprocedure i want this Rakesh

J4amieC

SUK is a good name for that column - as in it SUKs having an awful structure. If you needed those values as discrete columns they should have been discrete to start with! Anyway, a partial solution for you; if you can guarantee the startindex and length of each part it's not too bad:

;WITH SukTable ([Suk]) AS
(
SELECT 'abc 111 bbb 1212'
UNION SELECT 'pqr 222 ccc 2323'
)
SELECT
SUBSTRING(SUK,1,3) AS Field1,
SUBSTRING(SUK,5,3) AS Field2,
SUBSTRING(SUK,9,3) AS Field3,
SUBSTRING(SUK,12,5) AS Field4
FROM SukTable

Otherwise, assuming the parts are separated by a single space, its possible but becomes a dog very quickly - here's a solution for the first 2 parts, already becoming unweildly:

;WITH SukTable ([Suk]) AS
(
SELECT 'abc 111 bbb 1212'
UNION SELECT 'pqr 222 ccc 2323'
)
SELECT
LEFT(SUK,CHARINDEX(' ',Suk,0)-1) AS Field1,
SUBSTRING(SUK,CHARINDEX(' ',Suk,0),CHARINDEX(' ',Suk,CHARINDEX(' ',Suk,0))-CHARINDEX(' ',Suk,0)) AS Field2
FROM SukTable

Edit: Another option is something like this: http://stackoverflow.com/questions/697519/split-function-equivalent-in-tsql[^] Fix your data model, before it's too late.

rakeshs312

IT IS AUTOMATICALLY EXPANDING TABLE,VALUES MAY CHANGE,ONLY COMMON THING IS ONLY THE SPACE BETWEEN THEM.SO ON THAT WAY .......

J4amieC

DONT SHOUT!!!! 2) I have no idea what you just told me

PIEBALDconsult

Do it after you fetch the data into your code.

Rajesh Anuhya

Here i am giving a scalar function to active this..,

GO
/****** Object: UserDefinedFunction [dbo].[SplitGETPosFunc] Script Date: 10/20/2010 15:22:03 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[SplitGETPosFunc](@String varchar(8000),
@Delimiter char(1),
@POSITION INT)
RETURNS varchar(1000)
WITH EXECUTE AS CALLER
AS
begin
declare @idx int;
DECLARE @CHARIDX INT;
DECLARE @INPUTSTR VARCHAR(8000);
declare @OUTPUT varchar(1000);
SET @INPUTSTR=@String;
SET @CHARIDX =0;
SET @idx=0;
if (substring(@String,1,1)='S')
begin
while @idx < @POSITION
begin
SET @CHARIDX= CHARINDEX(@Delimiter, @INPUTSTR);
SET @OUTPUT=SUBSTRING(@INPUTSTR, 1, @CHARIDX - 1)
SET @INPUTSTR = SUBSTRING(@INPUTSTR,@CHARIDX +1,LEN(@INPUTSTR));
SET @idx=@idx+1;
end
end
else
set @OUTPUT=@String;
return @OUTPUT;
end

@String = original string @Delimiter = delemeter (in your case space) @POSITION INT = position of the word(starts from 1) function will return the word in the specified location Thanks & Regards Rajesh B

Rajesh B --> A Poor Workman Blames His Tools <--