• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 362
  • Last Modified:

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




0
Ludique
Asked:
Ludique
  • 7
  • 4
  • 2
1 Solution
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
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 Access MVP)Database ArchitectCommented:
CountryX      RegionX
Ontario      CAN
Nevada      USA
Bretagne      FR
Cornwall      UK

mx
0
 
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
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 Access MVP)Database ArchitectCommented:
Right ...
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
How many variations are there?

mx
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
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
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
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
 
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
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.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

  • 7
  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now