Search for values within a comma-separated string

Question

Post reply

Search for values within a comma-separated string

DataAnalyst110

Ten Centuries

Points: 1361
More actions
June 28, 2012 at 10:10 am

#260650

I need some help pulling a comma-separated value out of a string. I'm wanting to get the last comma-separated value out of StringField (see below). Here is some sample data:
CREATE TABLE TestingString
(
StringIDCHAR(10)NOT NULL,
StringFieldVARCHAR(100)NOT NULL,
);
INSERT INTO TestingString
(
StringID,
StringField
)
VALUES
('1', 'DR-2016,Doe,John,990016'),
('2', 'PM-2015,Smith,Ann,458376'),
('3', 'PM-2015,Alexander,Spencer,910578'),
('4', 'OR-2015,Cowper,William,102856')
;
--Test the script
SELECT *
FROM TestingString
I need to get the last number out into it's own column. So it may look something like this:
StringID StringFieldNum
1 990016
2 458376
3 910578
4 102856

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

sturner SSC-Insane Points: 22411 More actions · Answer 1

One of the easiest ways is to use reverse.

select StringID,

Reverse(LEFT(reverse(StringField),charindex(',',reverse(StringField))-1)) as StringField

If you need more complex parsing of the column search for Jeff Moden's csv splitter articles.

The probability of survival is inversely proportional to the angle of arrival.

Lowell SSC Guru Points: 323518 More actions · Answer 2

excellent job providing the consumable DDL and insert statements!

Thank you!

here's a neat trick that might not work, depending on your data;

it looks like comma separated, always 4 values, and the values don't contain a comma themselves.

if that's true, you can use the PARSENAME function to chop up the string easily, and get all 4 values;

it's usually used to chop up IP addresses or strings for ServerName.DatabaseName.SchemaName.TableName;

--Test the script

SELECT *,

PARSENAME(REPLACE(StringField,',','.'),4) As PartName,

PARSENAME(REPLACE(StringField,',','.'),3) As LastName,

PARSENAME(REPLACE(StringField,',','.'),2) As FirstName,

PARSENAME(REPLACE(StringField,',','.'),1) As PartCode

FROM TestingString

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

DataAnalyst110 Ten Centuries Points: 1361 More actions · Answer 3

Lowell,

Thank you for the reply - that works perfect! Would you mind explaining how it works. I found a PARSENAME article on MSDN. But you're adding in the REPLACE also. I'm trying to learn so it would be a help. But I understand if you have too much going on at the moment! Thanks again...

Lowell SSC Guru Points: 323518 More actions · Answer 4

sure!

parsename is just a handy built in function. built in functions will almost always perform better than code you build yourself, so if i can squeeze data into it's expected format, it's good to use it.

ParseName assumes the passed in string has periods in it to be treated as delimiters, and also assumes a maximum of 4 "parts"

the replace is just to force our actual data to fit the expected pattern for that function to work with.

here's another version that Jeff Moden posted, that allows you to select the splitter delimiter, and allows more than 4 "parts" as well;

select dbo.fn_parsename(StringField,',',1)

from TestingString

CREATE FUNCTION dbo.fn_parsename

(

@pString VARCHAR(7999),

@pDelimiter CHAR(1),

@Occurrance int

)

RETURNS VARCHAR(8000)

AS

BEGIN

DECLARE @Results VARCHAR(8000)

--===== "Inline" CTE Driven "Tally Table” produces values up to

-- 10,000... enough to cover VARCHAR(8000)

WITH

E1(N) AS ( --=== Create Ten 1's

SELECT 1 UNION ALL SELECT 1 UNION ALL

SELECT 1 UNION ALL SELECT 1 --10

),

E2(N) AS (SELECT 1 FROM E1 a, E1 b), --100

E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10,000

cteTally(N) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT N)) FROM E4) ,

--===== Do the split

InterResults

AS

(

SELECT ROW_NUMBER() OVER (ORDER BY N) AS ItemNumber,

SUBSTRING(@pString, N, CHARINDEX(@pDelimiter, @pString + @pDelimiter, N) - N) AS Item

FROM cteTally

WHERE N < LEN(@pString) + 2

AND SUBSTRING(@pDelimiter + @pString, N, 1) = @pDelimiter

)

SELECT @Results = Item FROM InterResults WHERE ItemNumber = @Occurrance

return @Results

END --FUNCTION

GO

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Jeff Moden SSC Guru Points: 1004354 More actions · Answer 5

Lowell,

I'm thinking that DelimitedSplit8K will do the trick here as well as opening the OP up to "other" possibilities for other things... like when the requirement comes to splitting 5 elements.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

DataAnalyst110 Ten Centuries Points: 1361 More actions · Answer 6

...I just stumbled upon the DelimitedSplit8K. Looks really good, although I've never seen anything quite like it before. I'm going to push it around in my test environment, but I will probably come back with more questions.

I just want to send a general "thanks" to the folks in this forum. You folks are sure patient with rookies 🙂