Fetch only country name

Question

Post reply

Fetch only country name

kiran.rajenimbalkar

SSCrazy

Points: 2603
More actions
November 18, 2014 at 2:47 am

#301138

Hi Sir,
I need your help to resolve this issue
I dont know the exact position of '/',',','--' or any special character.
I have to fetch first name of the country.
create table abc
(country nvarchar(200))
go
INSERT INTO abc VALUES ('Tennessee/Virginia,USA')
GO
INSERT INTO abc VALUES ('Kansas,USA')
GO
INSERT INTO abc VALUES ('Ohio, USA')
GO
INSERT INTO abc VALUES ('JAPAN--(Kagoshima and Miyazaki)')
GO
INSERT INTO abc VALUES ('SouthDakota/Iowa,USA')
GO
INSERT INTO abc VALUES ('INDIA--(parts of Gujarat')
GO
INSERT INTO abc VALUES ('USA--(New Jersey)')
GO
Below I want the result from my table abc
Tennessee
Kansas
Ohio
JAPAN
SouthDakota
INDIA
USA

Viewing 8 posts - 1 through 8 (of 8 total)

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

Koen Verbeeck SSC Guru Points: 259197 More actions · Answer 1

You could replace all of the special characters with one common special character, for example |.

Then you could split the data using a splitter function[/url] and get the first term.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Koen Verbeeck SSC Guru Points: 259197 More actions · Answer 2

ps: Tennessee, Kansas, Ohio and SouthDakota are not countries, but states.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Lowell SSC Guru Points: 323546 More actions · Answer 3

by making the assumptions outlined below, you can use patindex to find the beginning of the first non-character letter, and grab the substring.

the desired string always starts the string.

some character that is NOT A-Z is the delimiter.

note you need to take the position into consideration, which uis why my second column removes one from where the pattern starts, which leaves you with just the desired string.

select substring(country,1,patindex('%[^A-Z]%',country) ) ,

substring(country,1,patindex('%[^A-Z]%',country) -1 ) from abc

/*results

----- --------------------------------------

Tennessee/ Tennessee

Kansas, Kansas

Ohio, Ohio

JAPAN- JAPAN

SouthDakota/ SouthDakota

INDIA- INDIA

*/

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!

kiran.rajenimbalkar SSCrazy Points: 2603 More actions · Answer 4

Thanks Sir for your solution it worked.

but getting error while executing my code by adding your solution for getting country name.

"Invalid length parameter passed to the LEFT or SUBSTRING function"

I have some below data as well in my table

INSERT INTO abc VALUES ('(Part of) AUSTRIA')

GO

INSERT INTO abc VALUES ('(Part of) AUSTRIA; CROATIA; HUNGARY; SLOVENIA')

GO

INSERT INTO abc VALUES ('(Part of) SWEDEN')

GO

from this i would like fetch

AUSTRIA

SWEDEN

Regards,

Kiran

Koen Verbeeck SSC Guru Points: 259197 More actions · Answer 5

It would be nice if you would state all of your requirements in your initial question, instead of changing them halfway.

The simplest solution would be to replace '(Part of) ' with an empty string.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Lowell SSC Guru Points: 323546 More actions · Answer 6

yep your error goes directly to y assumptions: based on the sample data, the desired string always exists at the beginning.

Koen's suggestion is easily the fastest, but there's probably other data that also need to be tweaked.

the desired string always starts the string.
some character that is NOT A-Z is the delimiter.

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!

kiran.rajenimbalkar SSCrazy Points: 2603 More actions · Answer 7

Thanks Sir for your reply its working for me thanks a lot