How to split sql data in my database

Posted by Jayakumars under ASP.NET on 9/29/2011 | Points: 10 | Views : 2212 | Status : [Member] [MVP] | Replies : 2
Hi
How to split sql data
my sql record like this II-A i need how to split in sql server Delimiter value '-'
i need output

Field1 Field2
II A

Mark as Answer if its helpful to you


Responses

Posted by: Saranram on: 9/29/2011 [Member] Starter | Points: 25

Up
0
Down
Hi ,

Spliting string using delimiter

CREATE Function [dbo].[fnSplitter]
(
@IDs Varchar(max) --A big string which may have delimeter in it or not
,@Delimiter Varchar(1) -- Delimeter to use for splitting up the given string
)
/*********************** RETURN *********************/
--Returns the table with specific values in a temporary table. Useful especially if you have any IDs in the
--given string and want to get them as a table row values.
-- Example:
--@IDs = 1-2-3-4-5-6-7-8-9-10
--@Delimeter = '-'
--Returns @Tbl_IDS, which is having 10 rows with above IDS in each row by splitting up with given delimeter [in this example '-']
/****************************************************/
Returns @Tbl_IDs Table (ID Varchar(500)) As
Begin
--Remove the leading delimiter if any
while (substring(@IDs,1,1) =@Delimiter)
set @IDs = substring(@IDs, 2,len(@IDs)-1)

-- Append comma
Set @IDs = @IDs + @Delimiter

-- Indexes to keep the position of searching
Declare @Pos1 Int
Declare @pos2 Int
Declare @RowNum Int

-- Start from first character
Set @Pos1=1
Set @Pos2=1
While @Pos1
Begin
Set @Pos1 = CharIndex(@Delimiter,@IDs,@Pos1)
Insert @Tbl_IDs Values (Substring(@IDs,@Pos2,@Pos1-@Pos2))
-- Go to next non comma character
Set @Pos2=@Pos1+1
-- Search from the next charcater
Set @Pos1 = @Pos1+1
End
Return
End

This is not a very complex logic to understand. And below is the usage of the function.

DECLARE @EmployeeIDs VARCHAR(MAX);
SET @EmployeeIDs = '1-2-3-4-5-6-7-8-9-10';
SELECT * FROM [Employee] as e INNER JOIN dbo.fnStringSplitter(@EmployeeIDs, '-') as eIDs ON e.EmployeeID = eIDs.ID;




Saran

Jayakumars, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Niladri.biswas on: 10/2/2011 [Member] Platinum | Points: 25

Up
0
Down
Try this

Declare  

@InputString Varchar(max) = 'II-A'
,@Delimiter Varchar(2) = '-'


Select
Field1 = Left(@InputString,CHARINDEX(@Delimiter, @InputString) -1)
,Field2 = Right(
@InputString
,Len(@InputString) - CHARINDEX(@Delimiter, @InputString)
)
/* Result */
Field1 Field2
II A

Hope this helps

Best Regards,
Niladri Biswas

Jayakumars, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response