How to extract particular strings between two special characters in sql

Question

How to write TSQL script to get "23045" out of ",23045,23178,80978," . Thanks.

Answer 1

Here is a quick way how you can split those values up. If you like you could place that data into a temp table if you want to filter further. I've commented out the extra AND clause to match on your string so that you can see the full list

-- Drop counter table if it exists
IF OBJECT_ID('dbo.Results', 'U') IS NOT NULL DROP TABLE dbo.Results; 

-- Create a counter
SELECT TOP 1000 
IDENTITY(INT,1,1) AS N  
INTO dbo.Results
FROM Master.dbo.SysColumns sc1,       
Master.dbo.SysColumns sc2     

DECLARE @Test AS VARCHAR(100)
SET @Test = ',23045,23178,80978,'

-- Split the data
SELECT SUBSTRING(@Test,N+1,CHARINDEX(',',@Test,N+1)-N-1) AS 'Result' 
FROM dbo.Results
WHERE N < LEN(@Test)   
AND SUBSTRING(@Test,N,1) = ','
--AND SUBSTRING(@Test,N+1,CHARINDEX(',',@Test,N+1)-N-1) = '23045'