0

In my Table there is address column. Each part of the address is separated by a comma. For example:

No 50,ABC Street,New York.

Now I want to get only 'New York'. There are values like 'niw yok,niv york,nev yoke' in other addresses, so I just want the word after last comma.

How can I achieve this?

Larnu
  • 61,056
  • 10
  • 27
  • 50
Hasan Dulanga
  • 1
  • 5
  • 1
    Will the city always be the last entry? Will your address always be made up of 3 separate parts, or could it only have 2, perhaps 4, or even 5? What attempts have you made so far? – Larnu Feb 09 '19 at 12:00
  • 1
    Can you please share the queries that you tried? Please note that this is not the place to answer your questions, We will clear others doubts and correct the codes :). –  Feb 09 '19 at 12:02
  • @Larnu yes. last entry is the city. yes. they have 2,3,4 or may be 6. but last line is definitely a city. can't make a city list as New York may in several ways 'niw yok,niv york,nev yoke' – Hasan Dulanga Feb 09 '19 at 12:07
  • @nico haase I didn't try any thing. I want a function that can use to substring from last comma to end of the word – Hasan Dulanga Feb 09 '19 at 12:08
  • 1
    Duplicate https://stackoverflow.com/questions/1024978/find-index-of-last-occurrence-of-a-sub-string-using-t-sql –  Feb 09 '19 at 12:12
  • 1
    @xavierissac THank u sir. It works – Hasan Dulanga Feb 09 '19 at 12:15
  • create table ATABLE ( Cd varchar(30) ); insert into ATABLE values ('No 50,ABC Street,New York.'); insert into ATABLE values ('No 33,Via Roma,Tribano.'); insert into ATABLE values ('No. 1,Via Rossi,Monselice.'); SELECT *, REPLACE(SUBSTRING(Cd,CHARINDEX(',',Cd,CHARINDEX(',',Cd )+1)+1,LEN(Cd)),'.','') FROM ATABLE – A. Lion Feb 09 '19 at 14:56

0 Answers0