I have a scenario to select individual values from delimited string.
Example:
declare @Test varchar(100) = 'ABC|DEF|GHI'
Since the database is SQL Server 2008 version I cannot use split function.
Looking for the best way to split and select each values separately.
You can work something with CharIndex() to find your delimiters and then use SubString() to grab between them:
Declare @Test varchar(100) = 'ABC|DEF|GHI'
Declare @d1 Int = CharIndex('|',@Test,1)
Declare @d2 Int = CharIndex('|',@Test,@d1+1)
Select
Substring(@Test,1,@d1-1) As f1,
Substring(@Test,@d1+1,@d2-@d1-1) As f2,
Substring(@Test,@d2+1,len(@Test)-@d2) As f3
Result:
f1 f2 f3
ABC DEF GHI
You can create your own custom function in SQL server to split the string. You can use the below function for this...
Create FUNCTION SplitString(@str VARCHAR(8000), @delimiter Varchar(1))
RETURNS @Temp Table ( item varchar(100))
as Begin
Declare @i as int=0
Declare @j as int=0
Set @j = (Len(@str) - len(REPLACE(@str,@delimiter,'')))
While @i < = @j
Begin
if @i < @j
Begin
Insert into @Temp
Values(SUBSTRING(@str,1,Charindex(@delimiter,@str,1)-1))
set @str = right(@str,(len(@str)- Charindex(@delimiter,@str,1)))
End
Else
Begin
Insert into @Temp Values(@str)
End
Set @i = @i + 1
End
return;
End
go
select * from dbo.SplitString('ABC|DEF|GHI', '|')
This is too long for a comment. The best approach is to not use delimited values. Instead, you could use a table variable:
declare @Test table (val varchar(100));
insert into @Test(val)
values ('ABC', 'DEF', 'GHI');
Voila. No issue.
