INSERT INTO MYADD VALUES(1, 'address = h no. 532 St. No. 99 Lane 6, Mall road ISLAMABAD'); INSERT INTO MYADD VALUES(2, 'address = h no. 532 St. No. 99 Lane 6, Mall end road PESHAWAR'); INSERT INTO MYADD VALUES(3, 'address = h no. 532 St. No.1 Mall end road PESHAWAR');
WITH RECURSIVE ADDTBL(ID, ADDRESS, LVL, POS) AS (
SELECT ID, ADDRESS, 0 LVL, POSITION(' ' IN ADDRESS) POS FROM MYADD
SELECT ID, SUBSTRING(ADDRESS FROM POS+1) NEWADD, LVL+1 NEWLVL, POSITION(' ' IN NEWADD) NEWPOS FROM ADDTBL WHERE POS > 0
SELECT ID, ADDRESS AS CITY FROM ADDTBL QUALIFY ROW_NUMBER() OVER(PARTITION BY ID ORDER BY LVL DESC) = 1 ;
replace "ID" in your SQL with the relevant key columns of your table.
Adeel bhai. I dont like to remember the city name in advance or taking cityname from from lookup table and checking it in address field. It will increase the time complexity when the lookup table get larger,
in my case every thing is straight forward,I assumed that cityname will always lie at the end of address fileld.
I solved this problem by following way. length = length address then cityname = substring(length-12, 12);
it give me text of size 12 chars, but some time it contain a space,therefore I again substring it using Index of space, and extract the last part, which is cityname.