CODEWITHSUNDEEP
sqlsql-servert-sql
Rakhmi
Rakhmi

Reputation: 33

How to get value from data structure in column

I have data in a table and I am using SQL Server as follow:

Number Value
1 /F10749180509 1/TOYOTA TSUSHO ASIA PACIFIC PTE. L 1/TD. 2/600 NORTH BRIDGE ROAD HEX19 01, P 3/SG/ARKVIEWSQUARE SINGAPORE 188778
2 /0019695051 1/PT ASURANSI ALLIANZ LIFE 1/INDONESIA 2/ALLIANZ TWR 16FL JL.HRRASUNA SAID 3/ID/JAKARTA

As you can see on the table, I need to find Country code from field value. The country code can be found in string after "3/". The example from the first row, I need to get "SG" after 3/ and the second row I need to get "ID" after 3/ and so on. Actually If I copy the first data from value field to notepad, the data separated by new line. The data will be like:

/F10749180509
1/TOYOTA TSUSHO ASIA PACIFIC PTE. L
1/TD.
2/600 NORTH BRIDGE ROAD HEX19 01, P
3/SG/ARKVIEWSQUARE SINGAPORE 188778

Please help to find the query to get country code. Thank you

Upvotes: 0

Views: 115

Answers (2)

Venkataraman R
Venkataraman R

Reputation: 12979

You can use CHARINDEX to get the data.

declare @table table(number int, val varchar(8000))

insert into @table 
values
(1, '/F10749180509 1/TOYOTA TSUSHO ASIA PACIFIC PTE. L 1/TD. 2/600 NORTH BRIDGE ROAD HEX19 01, P 3/SG/ARKVIEWSQUARE SINGAPORE 188778')

select substring(val,charindex('3/',val,1)+2,2) from @table

SG

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522501

We might be able to use PATINDEX here along with SUBSTRING. Assuming that the country code would always be exactly two uppercase letters, we can try:

SELECT val, SUBSTRING(val, PATINDEX('% [0-9]/[A-Z][A-Z]/%', val) + 3, 2) AS country_code
FROM yourTable

Demo

Upvotes: 1

Related Questions