Query to split string from comma

Prasad Anumolu

10y1.3k

DECLARE @str varchar(50)='Hello, world'; /* SQL SERVER QUERY TO SPLIT STRING FROM COMMA AND GET LEFT AND RIGHT PART */

SELECT LEFT(@str, CHARINDEX(',', @str)-1) as LeftPart, RIGHT(@str, LEN(@str)-CHARINDEX(',', @str)) as RightPart;

Error is

Must declare the scalar variable "@str"

why it is coming ? if any one know tell me friends

5 Replies

Know the answer? Post it — somebody with the same question will find it here.

Raja T10y ago
Hello Prasad, you are most welcome.
Prasad Anumolu10y ago
thank you sir....
Raja T10y ago
Hello Prasad, Split function in not by default in sql server. U can create custom function sql server after u call the function it returns expected out put. For example try like below

Sql Function

CREATE FUNCTION [dbo].[Split]
(
@List nvarchar(2000),
@SplitOn nvarchar(5)
)
RETURNS @RtnValue table
(
Id int identity(1,1),
Value nvarchar(100)
)
AS
BEGIN
While (Charindex(@SplitOn,@List)>0)
Begin
Insert Into @RtnValue (value)
Select
Value = ltrim(rtrim(Substring(@List,1,Charindex(@SplitOn,@List)-1)))
Set @List = Substring(@List,Charindex(@SplitOn,@List)+len(@SplitOn),len(@List))
End
Insert Into @RtnValue (Value)
Select Value = ltrim(rtrim(@List))
Return
END

Example call the function
DECLARE @str varchar(50) = 'Hello, world';

SELECT * FROM dbo.[Split]( @str, ',' );
Results / Output like

Id    Value
1       Hello
2       world
Sujeet Suman10y ago
Please once try below query:

/* SQL SERVER QUERY TO SPLIT STRING FROM COMMA AND GET LEFT AND RIGHT PART */
DECLARE @str varchar(50) = 'Hello, world';

SELECT splited_values FROM dbo.SplitString( str, ',' );

If this is helpful for you then please accept my Answer.
Chandu Kumawat10y ago
refer this link http://www.aspsnippets.com/Articles/Split-and-convert-Comma-Separated-Delimited-String-to-Table-in-SQL-Server.aspx