You can try recursive queries , or a plain UDF which picks the last word in the text.the recursive solution is like this ...CREATE TABLE MYADD(ID INTEGER,ADDRESS VARCHAR(100))UNIQUE PRIMARY INDEX(ID);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) POSFROM MYADDUNION ALLSELECT ID, SUBSTRING(ADDRESS FROM POS+1) NEWADD, LVL+1 NEWLVL, POSITION(' ' IN NEWADD) NEWPOS FROM ADDTBLWHERE POS > 0)SELECT ID, ADDRESS AS CITYFROM ADDTBLQUALIFY ROW_NUMBER() OVER(PARTITION BY ID ORDER BY LVL DESC) = 1;replace "ID" in your SQL with the relevant key columns of your table.
i got following errorError3706: Syntex error: expected something between the beginning of the request and the 'WITH' keyword
It's the exact steps I executed.What version of TD are your running ?
taraData SQL Assistant 6.1.0 Demo verion
you shouldn't be having any trouble executing that SQL in 6.1Are you executing the entire contents in a single go ? it looks like there's something in your SQL assistant before the recursive statement itself.Try putting the contents of the recursive query alone in a window and execute them (assuming you already created the table and inserted the records).
Hello,Do you know the names of all the cities which may be present in the data?Regards,Adeel
-- If you are stuck at something .... consider it an opportunity to think anew.
WITH RECURSIVE ADDTBL(ID, ADDRESS, LVL, POS)AS(SELECT ID, ADDRESS, 0 LVL, POSITION(' ' IN ADDRESS) POSFROM MYADDUNION ALLSELECT ID, SUBSTRING(ADDRESS FROM POS+1) NEWADD, LVL+1 NEWLVL, POSITION(' ' IN NEWADD) NEWPOS FROM ADDTBLWHERE POS > 0)--i encountered by same error when i executed only the above code fragment.Error3706: Syntex error: expected something between the beginning of the request and the 'WITH' keyword
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.but i want to solve it by the way of joedsilva
Recursive queries have been implemented in V2R6, but your other posts show you're runng a V2R5 demo.Another reason to ugrade to a TD12 demo :-)Dieter
Dieter
how to extract Islamabad and PESHAWAR from following addresses, using sql functionaddress = h no. 532 St. No. 99 Lane 6, Mall road ISLAMABADaddress = h no. 532 St. No. 99 Lane 6, Mall end road PESHAWARaddress = h no. 532 St. No.1 Mall end road PESHAWAR