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
Favourite Performance Tuning Tricks
How to Write a SQL Delete Statement with a Select Statement in the Where Clause
How to Export Image Field to File
How to Use an Alias in a Postgresql Order by Clause
Update with Join Query in Oracle
SQL Server Select Where Any Column Contains 'X'
Modify Default Value in SQL Server
Differencebetween 'Yyyy' and 'Rrrr' in Oracle SQL
Select (Retrieve) All Records from Multiple Schemas Using Postgres
Postgresql Order by Issue - Natural Sort
Differencebetween Views and Materialized Views in Oracle
Discard Millisecond Part from Timestamp
Entity Framework - Attribute in Clause Usage
Improving Performance of Cluster Index Guid Primary Key
Differencebetween SQL, Pl-SQL and T-Sql