taraData substring function

Database
Enthusiast

taraData substring function

how to extract Islamabad and PESHAWAR from following addresses, using sql function

address = h no. 532 St. No. 99 Lane 6, Mall road ISLAMABAD
address = h no. 532 St. No. 99 Lane 6, Mall end road PESHAWAR
address = h no. 532 St. No.1 Mall end road PESHAWAR
9 REPLIES
Enthusiast

Re: taraData substring function

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) POS
FROM MYADD

UNION ALL

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

Re: taraData substring function


i got following error

Error
3706: Syntex error: expected something between the beginning of the request and the 'WITH' keyword
Enthusiast

Re: taraData substring function

It's the exact steps I executed.

What version of TD are your running ?
Enthusiast

Re: taraData substring function

taraData SQL Assistant 6.1.0 Demo verion

Enthusiast

Re: taraData substring function

you shouldn't be having any trouble executing that SQL in 6.1

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

Teradata Employee

Re: taraData substring function

Hello,

Do you know the names of all the cities which may be present in the data?

Regards,

Adeel
Enthusiast

Re: taraData substring function


WITH RECURSIVE ADDTBL(ID, ADDRESS, LVL, POS)
AS
(

SELECT ID, ADDRESS, 0 LVL, POSITION(' ' IN ADDRESS) POS
FROM MYADD

UNION ALL

SELECT ID, SUBSTRING(ADDRESS FROM POS+1) NEWADD, LVL+1 NEWLVL, POSITION(' ' IN NEWADD) NEWPOS
FROM ADDTBL
WHERE POS > 0

)
--
i encountered by same error when i executed only the above code fragment.

Error
3706: Syntex error: expected something between the beginning of the request and the 'WITH' keyword

Enthusiast

Re: taraData substring function

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
Senior Apprentice

Re: taraData substring function

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