Issue in Substr and Index with CASE

Database
Enthusiast

Issue in Substr and Index with CASE

Hi I am having an issue with below syntax:

When I run below query 

SELECT URL_Val, SUBSTR (Click.URL_Val, INDEX(Click.URL_Val,'artist/')+7, 10) FROM DB.A Click
WHERE Click.URL_Val LIKE '%artist%'

I get below result

URL_Val                                                                             SUBSTR


However, when I try to do it using a Case statement,


SELECT 

Click.a

,Click.b

, Click.c

, Click.d

, Click.URL_Val

, CASE 

WHEN Click.URL_Val LIKE '%event%'  

THEN SUBSTR (Click.URL_Val, INDEX(Click.URL_Val,'event/')+6, 16)

WHEN Click.URL_Val LIKE '%artist/%'

THEN  SUBSTR (Click.URL_Val, INDEX(Click.URL_Val,'artist/')+7, 10  ) 

END

,Click.e

,P.f

,p.g

,CAST(CAST(click.h AS DATE) AS TIMESTAMP(0))

FROM DB.ABC Click

INNER JOIN DBA.PAT p ON click.e = patron.e;

I get below result for artist row. 


URL_Val                                                                             SUBSTR


I need to get result as mentioned in the first result set. Am I missing something here? How come the extraction is different in second result set? Please help.

Thanks,

Nik

4 REPLIES
Enthusiast

Re: Issue in Substr and Index with CASE

Sorry for the missing code window for second piece. Here it is

SELECT 
Click.a
,Click.b
, Click.c
, Click.d
, Click.URL_Val
, CASE
WHEN Click.URL_Val LIKE '%event%'
THEN SUBSTR (Click.URL_Val, INDEX(Click.URL_Val,'event/')+6, 16)
WHEN Click.URL_Val LIKE '%artist/%'
THEN SUBSTR (Click.URL_Val, INDEX(Click.URL_Val,'artist/')+7, 10 )
END
,Click.e
,P.f
,p.g
,CAST(CAST(click.h AS DATE) AS TIMESTAMP(0))
FROM DB.ABC Click
INNER JOIN DBA.PAT p ON click.e = patron.e;
Junior Contributor

Re: Issue in Substr and Index with CASE

Hi Nik,

//ABC.com/arti is really strange, seems like it didn't find 'artist' and then started at 0 + 6, but returned only 14 instead of 16 characters.

SELECT
'http://ABC.com/artist/123456/HANSON:-EMLN_690635' AS URL_Val,
CASE
WHEN URL_Val LIKE '%event%'
THEN SUBSTR (URL_Val, INDEX(URL_Val,'event/')+6, 16)
WHEN URL_Val LIKE '%artist/%'
THEN SUBSTR (URL_Val, INDEX(URL_Val,'artist/')+7, 10 )
END

returns 123456/HAN as expected.

What if you change WHEN URL_Val LIKE '%artist/%' to WHEN INDEX(URL_Val,'artist/') > 0?

You might also have a look a REGEXP_SUBSTR, might be easier depending on what part of the string you try to extract. 

Enthusiast

Re: Issue in Substr and Index with CASE

Hi Dieter,

Here is a bit modification on the previous issue.

I am trying to query a table that will have event and artist information in URLs. This step removes the artist and event info from the URL. Each URL will have either artist or event info but never both. So I am using case.

If run separately like you mentioned, the results are fine. But if put in a case statement as specified by the big select(which is part of an insert statement), it does not work and does not find the 'artist' part.

Here is the weird pard. If we change the order of 'event' and 'artist' as below,

SELECT
Click.a
,Click.b
, Click.c
, Click.d
, Click.URL_Val
, CASE
WHEN Click.URL_Val LIKE '%artist/%'
THEN SUBSTR (Click.URL_Val, INDEX(Click.URL_Val,'artist/')+7, 10 )
WHEN Click.URL_Val LIKE '%event%'
THEN SUBSTR (Click.URL_Val, INDEX(Click.URL_Val,'event/')+6, 16)
END
,Click.e
,P.f
,p.g
,CAST(CAST(click.h AS DATE) AS TIMESTAMP(0))
FROM DB.ABC Click
INNER JOIN DBA.PAT p ON click.e = patron.e;

I get the same issue with event URL's but artist URLs are extracted correctly. 

I am trying your suggestion of WHEN INDEX(URL_Val,'artist/') > 0. Will update shortly. Meanwhile, any help on this issue is really appreciated.

Thanks,

Nik

Junior Contributor

Re: Issue in Substr and Index with CASE

Hi Nik,

you should open an incident with Teradata support for this issue.