How to split sql data in my database

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

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