Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have a following query in SQL server:
SELECT * FROM TABLE_A
DESCRIPTION NOT LIKE '%[^a-z]cos[^a-z]%'
So this query will return values only if it has the text COS as a whole, without any characters [a-z] before or after that.
If I use WILDMATCH ( DESCRIPTION, '*COS*'), it returns rows that have have words like 'ECOSYSTEM' which has characters [a-z] before and after. This is not desirable.
DESCRIPTION | SQL SERVER | QV WILDMATCH |
Abcde COS | TRUE | TRUE |
defgh COS | TRUE | TRUE |
Ecosystem | FALSE | TRUE |
Totalcost | FALSE | TRUE |
SumofCost | FALSE | TRUE |
So my question is what is the correct function or method in qlikview that will return exact same result as the SQL SERVER query I have above.
Thanks,
KS
WILDMATCH ( DESCRIPTION, '* COS*')
May be This?
= IF(Index(DESCRIPTION, 'COS')>1, 'TRUE', 'FALSE')
Hi Rodrigo, WILDMATCH(DESCRIPTION, '*COS*) will not produce desired result for me. It will return rows with words like ECOSYSTEM, which is not correct. I want only rows with text COS as a whole. There should be no other character other than blank spaces
Hell Kannan,
Did you get a chance to try my script sent earlier ??
Thanks,
V.
Thanks vishaggi. That works only there are no character [a-z] before or after. Unfortunately, the text I have has special characters such as hyphen. like '*-COS*' also. So I am wondering how to discriminate such strings.
Hi Vishaagi,
Sorry about the previous comment. Your solution works perfectly now. It works even if there are special characters. Thanks for your suggestion.
Regards,
KS
Hi Kannan,
I think you've not noticed that Rodrigo's expression using '* COS*' contained a space after the first *. This would not return ECOSYSTEM.
Cheers
Andrew
Thanks Rodrigo. I did not notice the blank space you had inserted like '* COS*'. So this works.
Hi ,
I am facing similar problem to search the word boundry but somehow not able to achieve. Could you please share the application or the logic you wrote to achieve
thanks in advance