Ludique
asked on
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
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
CountryX RegionX
Ontario CAN
Nevada USA
Bretagne FR
Cornwall UK
mx
Ontario CAN
Nevada USA
Bretagne FR
Cornwall UK
mx
ASKER
Apart from swapping RegionX and CountryX thus:
SELECT Left(Replace(Replace([Regi on],"(","" ),")",""), InStr(1,Re place(Repl ace([Regio n],"(","") ,")","")," ")-1) AS RegionX, Mid(Replace(Replace([Regio n],"(","") ,")",""),I nStr(1,Rep lace(Repla ce([Region ],"(",""), ")","")," ")+1) AS CountryX
FROM Regions;
Brilliant! Thank you very much.
SELECT Left(Replace(Replace([Regi
FROM Regions;
Brilliant! Thank you very much.
You are welcome
Yes, I had to alias those because I used those names in a table I created.
mx
Yes, I had to alias those because I used those names in a table I created.
mx
ASKER
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
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
Right ...
How many variations are there?
mx
mx
Standby ...
mx
mx
ASKER
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?
Wouldn't it be easier to cut it up on the parentheses rather than the spaces?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
That's it!
Lovely. Looks tidy too :)
Lovely. Looks tidy too :)
Hi,
Check out this.
Sample Code
- Bhavesh
Check out this.
SELECT SUBSTRING(RgnCtry, 1, CHARINDEX('(',RgnCtry)-1)Country,
SUBSTRING(RgnCtry, CHARINDEX('(',RgnCtry)+1, (CHARINDEX(')',RgnCtry)-CHARINDEX('(',RgnCtry)-1))Region
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))
- Bhavesh
Hi,
Sorry guys....
I provide solution for SQL Server....
Pls ignore... :-)
Sorry guys....
I provide solution for SQL Server....
Pls ignore... :-)
SELECT Left(Replace(Replace([RgnC
FROM tblRgn;
mx