Split Function in SQL Server 2008

Split function by comma in SQL Server 2008

I've change the function name so it won't overlapped in what the Split() function really does.

Here is the code:

CREATE FUNCTION dbo.GetColumnValue(
@String varchar(8000),
@Delimiter char(1),
@Column int = 1
)
returns varchar(8000)
as
begin

declare @idx int
declare @slice varchar(8000)

select @idx = 1
if len(@String)<1 or @String is null return null

declare @ColCnt int
set @ColCnt = 1

while (@idx != 0)
begin
set @idx = charindex(@Delimiter,@String)
if @idx!=0 begin
if (@ColCnt = @Column) return left(@String,@idx - 1)

set @ColCnt = @ColCnt + 1

end

set @String = right(@String,len(@String) - @idx)
if len(@String) = 0 break
end
return @String
end

And here is the usage:

select dbo.GetColumnValue('Col1,Field2,VAlue3', ',', 3)

split string in sql server 2008

Here is a working solution

declare @yearfrom varchar(2),@yearto varchar(2)

select @yearfrom=substring('0to0Years',0,patindex('%to%','0to0Years')),
@yearto=substring('0to0Years',patindex('%to%','0to0Years')+2,patindex('%Years%','0to0Years')-patindex('%to%','0to0Years')-2)

SqlFiddle: http://www.sqlfiddle.com/#!3/d41d8/12483

For working on your column replace '0to0Years' with column name

    declare @yearfrom varchar(2),@yearto varchar(2)

select @yearfrom=substring(col_name,0,patindex('%to%',col_name)),
@yearto=substring(,patindex('%to%',col_name)+2,patindex('%Years%',col_name)-patindex('%to%',col_name)-2)
from table_name where <condition>

Split string by comma in SQL Server 2008

Following will work -

DECLARE @A VARCHAR (100)= '4,5,6'
DECLARE @B VARCHAR (100)= 'RXXXXXX'
DECLARE @RETURN_VALUE BIT = 1 --DEFAULT 1


SELECT items
INTO #STRINGS
FROM dbo.split(@A,',')

IF EXISTS(SELECT 1 FROM #STRINGS S WHERE CHARINDEX(items, @B) > 0)
SET @RETURN_VALUE = 0

PRINT @RETURN_VALUE

DROP TABLE #STRINGS

You can also use CONTAINS instead of CHARINDEX -

IF EXISTS(SELECT 1 FROM #STRINGS S WHERE CONTAINS(items, @B))
SET @RETURN_VALUE = 0

T-SQL split string

I've used this SQL before which may work for you:-

CREATE FUNCTION dbo.splitstring ( @stringToSplit VARCHAR(MAX) )
RETURNS
@returnList TABLE ([Name] [nvarchar] (500))
AS
BEGIN

DECLARE @name NVARCHAR(255)
DECLARE @pos INT

WHILE CHARINDEX(',', @stringToSplit) > 0
BEGIN
SELECT @pos = CHARINDEX(',', @stringToSplit)
SELECT @name = SUBSTRING(@stringToSplit, 1, @pos-1)

INSERT INTO @returnList
SELECT @name

SELECT @stringToSplit = SUBSTRING(@stringToSplit, @pos+1, LEN(@stringToSplit)-@pos)
END

INSERT INTO @returnList
SELECT @stringToSplit

RETURN
END

and to use it:-

SELECT * FROM dbo.splitstring('91,12,65,78,56,789')

Mimic STRING_SPLIT without custom function in SQL Server 2008


DECLARE @Result Table(Value varchar(50))
DECLARE @x XML
SELECT @X = CAST('<A>' + REPLACE(@StringList, '|', '</A><A>') + '</A>' AS XML)

INSERT INTO @Result
SELECT t.value('.', 'varchar(50)') as inVal
FROM @X.nodes('/A') AS x(t)

This will create a table with one column (Value). Each split value from your pipe-delimited string will create a new record in this table. Then you can join to it however you'd like. Please let me know if this is unclear or if it doesn't work on SQL 2008.

You can increase the size of the varchar, if needed - and you can modify the query to split on different values (comma-delimited, etc.).

SQL Server 2008 split string fails due to ampersand

Edit your function and replace all & as &
This will remove the error. This happens because XML cannot parse & as it's an inbuilt tag.



Related Topics



Leave a reply



Submit