Extracting character string within quotation marks

I have a long list of descriptions in a character field and I need to extract only those characters within the quotation marks.
So if the string is The rain in 'Spain' stays mainly in the plains or Hurricanes 'Hardly' ever happen I want to extract Spain and Hardly.
So I need to locate the first quotation mark from the left and then the first quotation from the right and extract the letters between them.
LVL 1
rwheeler23Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Chris LuttrellSenior Database ArchitectCommented:
There are various ways to accomplish this, here is an example demonstrating one of them:
CREATE TABLE X (Id INT IDENTITY, Col_A VARCHAR(500));

INSERT INTO dbo.X ( Col_A )
VALUES ( 'The rain in ''Spain'' stays mainly in the plains');
INSERT INTO dbo.X ( Col_A )
VALUES ( 'Hurricanes ''Hardly'' ever happen');
INSERT INTO dbo.X ( Col_A )
VALUES ( 'No embedded commas here');

SELECT X.*, SUBSTRING(X.Col_A, Y.FirstOne+1, Y.SecondOne-Y.FirstOne-1) YourString
FROM dbo.X AS X
LEFT OUTER JOIN (
SELECT Id, CHARINDEX('''',X.Col_A,1) FirstOne, CHARINDEX('''',X.Col_A,CHARINDEX('''',X.Col_A,1)+1) SecondOne FROM dbo.X AS X) Y ON X.Id = Y.Id AND Y.FirstOne > 0;

DROP TABLE dbo.X;

Open in new window

outputedited to improve example to not error when no embedded commas were in the data.
0
rwheeler23Author Commented:
Is there another method without the Id column? I am not allowed to add columns to any table.
0
Chris LuttrellSenior Database ArchitectCommented:
sure, here are 2 other ways to accomplish the results without needing an Id column
CREATE TABLE X (Col_A VARCHAR(500));

INSERT INTO dbo.X ( Col_A )
VALUES ( 'The rain in ''Spain'' stays mainly in the plains');
INSERT INTO dbo.X ( Col_A )
VALUES ( 'Hurricanes ''Hardly'' ever happen');
INSERT INTO dbo.X ( Col_A )
VALUES ( 'No embedded commas here');

WITH tmpX AS
(
SELECT Col_A, CHARINDEX('''',X.Col_A,1) FirstOne, CHARINDEX('''',X.Col_A,CHARINDEX('''',X.Col_A,1)+1) SecondOne 
FROM dbo.X AS X)
SELECT X.*, CASE X.FirstOne WHEN 0 THEN NULL ELSE SUBSTRING(X.Col_A, X.FirstOne+1, X.SecondOne-X.FirstOne-1) END YourString
FROM tmpX AS X;

SELECT X.Col_A,
	   CASE CHARINDEX('''', X.Col_A, 1)
			WHEN 0 THEN NULL
			ELSE
				SUBSTRING(
					X.Col_A,
					CHARINDEX('''', X.Col_A, 1) + 1,
					CHARINDEX('''', X.Col_A, CHARINDEX('''', X.Col_A, 1) + 1) - CHARINDEX('''', X.Col_A, 1) - 1)
	   END AS YourString
FROM   dbo.X AS X;

DROP TABLE dbo.X;

Open in new window

output
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
rwheeler23Author Commented:
Thank you. Now I can use this list to give the users the list they need.
0
Chris LuttrellSenior Database ArchitectCommented:
Thanks! Glad I could help.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.