Query to show text in string enclosed in brackets eg Nevada (USA) -> USA

My table has a list like this:

REGION
Nevada (USA)
Ontario (CAN)
Bretagne (FR)
Cornwall (UK)

I would like a query that shows results like this:
Region      Country
Nevade     USA
Ontario     CAN
Bretagne   FR
Cornwall    UK

I've experimented with various combinations of Left, Right, Mid, InStr, InStrRev, etc but just can't get the syntax right




LudiqueAsked:
Who is Participating?
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Connect With a Mentor Commented:
Revised:

SELECT Left([RgnCtry],InStr(1,[RgnCtry],"(")-1) AS CountryX, Replace(Mid([RgnCtry],InStr(1,[RgnCtry],"(")+1),")","") AS RegionX
FROM tblRgn;


CountryX      RegionX
Ontario       CAN
Nevada       USA
Bretagne       FR
Cornwall       UK
New Mexico       USA

mx
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
Here you go:

SELECT Left(Replace(Replace([RgnCtry],"(",""),")",""),InStr(1,Replace(Replace([RgnCtry],"(",""),")","")," ")-1) AS CountryX, Mid(Replace(Replace([RgnCtry],"(",""),")",""),InStr(1,Replace(Replace([RgnCtry],"(",""),")","")," ")+1) AS RegionX
FROM tblRgn;


mx
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
CountryX      RegionX
Ontario      CAN
Nevada      USA
Bretagne      FR
Cornwall      UK

mx
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
LudiqueAuthor Commented:
Apart from swapping RegionX and CountryX thus:

SELECT Left(Replace(Replace([Region],"(",""),")",""),InStr(1,Replace(Replace([Region],"(",""),")","")," ")-1) AS RegionX, Mid(Replace(Replace([Region],"(",""),")",""),InStr(1,Replace(Replace([Region],"(",""),")","")," ")+1) AS CountryX
FROM Regions;

Brilliant! Thank you very much.
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
You are welcome

Yes, I had to alias those because I used those names in a table I created.

mx
0
 
LudiqueAuthor Commented:
Sorry, I spoke too soon

I just found out that if the Region has spaces it all goes wrong

e.g

New Mexico (USA)  comes out  
New                Mexico (USA)  instead of
New Mexico    USA
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
Right ...
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
How many variations are there?

mx
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
Standby ...

mx
0
 
LudiqueAuthor Commented:
Well lots I suppose.  A region name could be made up of several words.  

Wouldn't it be easier to cut it up on the parentheses rather than the spaces?
0
 
LudiqueAuthor Commented:
That's it!

Lovely.  Looks tidy too :)
0
 
Bhavesh ShahLead AnalysistCommented:
Hi,

Check out this.

SELECT	SUBSTRING(RgnCtry, 1, CHARINDEX('(',RgnCtry)-1)Country,
		SUBSTRING(RgnCtry, CHARINDEX('(',RgnCtry)+1, (CHARINDEX(')',RgnCtry)-CHARINDEX('(',RgnCtry)-1))Region

Open in new window


Sample Code

DECLARE @Input VarChar(50)

SET @Input = 'Nevada (USA)'
select CHARINDEX(')',@Input)-1

SELECT	SUBSTRING(@Input, 1, CHARINDEX('(',@Input)-1)Country,
		SUBSTRING(@Input, CHARINDEX('(',@Input)+1, (CHARINDEX(')',@Input)-CHARINDEX('(',@Input)-1))
		
		
		

 

Open in new window



- Bhavesh
0
 
Bhavesh ShahLead AnalysistCommented:
Hi,

Sorry guys....

I provide solution for SQL Server....

Pls ignore... :-)
0
All Courses

From novice to tech pro — start learning today.