T-SQL – split a string value based on comma or another delimiter

Possibly save 1 hour of your time: This shows how you can take a column that has data such as a string value with comma separated value or semi-colon separated value and process each value for other purpose. In my case I want to check for foreign key not exist.

</pre>
DECLARE @MyCursor CURSOR;
SET @MyCursor = CURSOR FOR
SELECT [column_with_delimiter_separated_values]
FROM [dbo].[YourTable]

DECLARE @textString varchar(500);

OPEN @MyCursor
FETCH NEXT FROM @MyCursor
INTO @textString

DECLARE @tempSplitText TABLE
(
SplitString nvarchar(500)
)

WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE @delimiter char = ';'
DECLARE @pos INT = 0
DECLARE @len INT = 0
DECLARE @value varchar(500)

WHILE CHARINDEX(@delimiter, @textString, @pos+1)>0
BEGIN
set @len = CHARINDEX(@delimiter, @textString, @pos+1) - @pos
set @value = SUBSTRING(@textString, @pos, @len)
INSERT INTO @tempSplitText(SplitString) VALUES(RTRIM(LTRIM(@Value)))
set @pos = CHARINDEX(@delimiter, @textString, @pos+@len) +1
END

IF (LEN(@region) > @len) --deals with last element
BEGIN
set @value = SUBSTRING(@textString, @pos, LEN(@textString)-@pos+1)
INSERT INTO @tempSplitText(TextString) VALUES(RTRIM(LTRIM(@Value)))
END

FETCH NEXT FROM @MyCursor
INTO @textString
END;

CLOSE @MyCursor;
DEALLOCATE @MyCursor;

select r. from @tempSplitText r
left join [dbo].[YourTableWithTheForeignKey] c on r.SplitString = c.ForeignKey
where c.ForeignKey is null
<pre>

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s