CODEWITHSUNDEEP
sql-servert-sql
user43698
user43698

Reputation: 39

split string by " " in a sql query

there is a string is "'CNY','THB','USD','VND'" pass from coding.

is there any way that can split it into 'CNY','THB','USD','VND' because I was doing a IN statement. It cannot be done with "'CNY','THB','USD','VND'".

Upvotes: 0

Views: 119

Answers (3)

Mez
Mez

Reputation: 4726

You can use this function, if you are using Microsoft SQL. It will return a table, and in your case you can easily specify if a string in question is in the result set of this table. I am showing you how to use it below

create FUNCTION [dbo].[SPLITSTRING]
(
@CSV varchar(max),
@Delimiter char(1)
)
RETURNS
@Split TABLE (Id int identity(1,1),[OutParam] varchar(max))
AS
BEGIN
Declare @Len as int, @Pos1 int, @Pos2 int
IF LTRIM(RTRIM(@CSV)) = ''
RETURN
SELECT @CSV = @Delimiter + @CSV + @Delimiter
select @Len = len(@csv), @Pos1 = 1


While @Pos1 < @Len
Begin
select @Pos2 = charindex(@Delimiter,@CSV,@Pos1 + 1)
insert @Split select ltrim(rtrim(substring(@csv, @Pos1+1, @Pos2 - @pos1 -1)))
select @Pos1 = @Pos2
End
RETURN
END

Then do 

select * from [dbo].[SPLITSTRING]('CNY,THB,USD,VND',',')

What I am doing is creating a table, and splitting out the string between ",", and returning a table.

Upvotes: 2

Amit Bisht
Amit Bisht

Reputation: 5136

Create a Table Vlaued Function 

Create FUNCTION [dbo].[Split]
(
@RowData nvarchar(2000),
@SplitOn nvarchar(5)
)  
RETURNS @RtnValue table 
(
   Data nvarchar(100)
)
AS  
BEGIN 
Declare @Cnt int
Set @Cnt = 1
While (Charindex(@SplitOn,@RowData)>0)
Begin
Insert Into @RtnValue (data)
Select 
Data = ltrim(rtrim(Substring(@RowData,1,Charindex(@SplitOn,@RowData)-1)))  
Set @RowData = Substring(@RowData,Charindex(@SplitOn,@RowData)+1,len(@RowData))  
Set @Cnt = @Cnt + 1  
End
Insert Into @RtnValue (data)
Select Data = ltrim(rtrim(@RowData))
Return
END

And Create a table where you want to use split string 

Declare @SplitString Table
(
SubjectName nvarchar(50)
)

and Call The Function 

INSERT INTO @SplitString SELECT * FROM dbo.Split("Your String",'Character from which you want to split')

Upvotes: 0

Singaravelan
Singaravelan

Reputation: 849

From above answer , no need of single quote. Pass the string as 'CNY,THB,USD,VND'

in query

Where Varchar_Field IN (select * from [dbo].[SPLITSTRING] ('CNY,THB,USD,VND',','))

Upvotes: 0

Related Questions