All Forums Database
jehan 12 posts Joined 11/08
08 Nov 2008
taraData substring function

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

joedsilva 505 posts Joined 07/05
09 Nov 2008

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.

jehan 12 posts Joined 11/08
09 Nov 2008

i got following errorError3706: Syntex error: expected something between the beginning of the request and the 'WITH' keyword

joedsilva 505 posts Joined 07/05
09 Nov 2008

It's the exact steps I executed.What version of TD are your running ?

jehan 12 posts Joined 11/08
09 Nov 2008

taraData SQL Assistant 6.1.0 Demo verion

joedsilva 505 posts Joined 07/05
09 Nov 2008

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).

Adeel Chaudhry 773 posts Joined 04/08
10 Nov 2008

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.

jehan 12 posts Joined 11/08
10 Nov 2008

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

jehan 12 posts Joined 11/08
10 Nov 2008

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

dnoeth 4628 posts Joined 11/04
10 Nov 2008

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

You must sign in to leave a comment.