August 15, 2018 at 9:16 am
Hi ,
I am trying to do a pattern search and my query is generic and it uses LIKE .
The requirement says does not begin with V21.
i should be able to use LIKE statement and achieve this .
select * from table A where a.column like '^V21%'.
^ - would alway check the first character. Is there a way to check all the first 3 characters ?
August 15, 2018 at 9:20 am
Hi,
You can you NOT LIKE in the below way:
select * from table A where a.column not like 'V21%'.
Does that satisfy your requirement or I am oversimplifying?
August 15, 2018 at 9:20 am
savioceg - Wednesday, August 15, 2018 9:16 AMHi ,I am trying to do a pattern search and my query is generic and it uses LIKE .
The requirement says does not begin with V21.i should be able to use LIKE statement and achieve this .
select * from table A where a.column like '^V21%'.
^ - would alway check the first character. Is there a way to check all the first 3 characters ?
Try WHERE NOT LIKE 'V21%'
August 15, 2018 at 9:34 am
Jonathan AC Roberts - Wednesday, August 15, 2018 9:20 AMsavioceg - Wednesday, August 15, 2018 9:16 AMHi ,I am trying to do a pattern search and my query is generic and it uses LIKE .
The requirement says does not begin with V21.i should be able to use LIKE statement and achieve this .
select * from table A where a.column like '^V21%'.
^ - would alway check the first character. Is there a way to check all the first 3 characters ?
Try
WHERE NOT LIKE 'V21%'
i cannot use not like in this scenario. the values go into a lookup table and i am joining with the lookup table and i can use only LIKE .
August 15, 2018 at 9:41 am
WHERE LEFT(a.Column,3) <> 'V21'
August 15, 2018 at 9:49 am
savioceg - Wednesday, August 15, 2018 9:34 AMJonathan AC Roberts - Wednesday, August 15, 2018 9:20 AMsavioceg - Wednesday, August 15, 2018 9:16 AMHi ,I am trying to do a pattern search and my query is generic and it uses LIKE .
The requirement says does not begin with V21.i should be able to use LIKE statement and achieve this .
select * from table A where a.column like '^V21%'.
^ - would alway check the first character. Is there a way to check all the first 3 characters ?
Try
WHERE NOT LIKE 'V21%'i cannot use not like in this scenario. the values go into a lookup table and i am joining with the lookup table and i can use only LIKE .
I think you need to amend your "generic query" to make it a bit more generic.
August 15, 2018 at 9:52 am
savioceg - Wednesday, August 15, 2018 9:34 AMJonathan AC Roberts - Wednesday, August 15, 2018 9:20 AMsavioceg - Wednesday, August 15, 2018 9:16 AMHi ,I am trying to do a pattern search and my query is generic and it uses LIKE .
The requirement says does not begin with V21.i should be able to use LIKE statement and achieve this .
select * from table A where a.column like '^V21%'.
^ - would alway check the first character. Is there a way to check all the first 3 characters ?
Try
WHERE NOT LIKE 'V21%'i cannot use not like in this scenario. the values go into a lookup table and i am joining with the lookup table and i can use only LIKE .
Ummm.... unless you are using something other than Microsoft's T-SQL, if you get to specify the query, anywhere you can use LIKE you can use NOT LIKE.
Thus I have no idea why you can't, nor any idea what makes you think that you can't...
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
August 15, 2018 at 10:22 am
savioceg - Wednesday, August 15, 2018 9:34 AMJonathan AC Roberts - Wednesday, August 15, 2018 9:20 AMsavioceg - Wednesday, August 15, 2018 9:16 AMHi ,I am trying to do a pattern search and my query is generic and it uses LIKE .
The requirement says does not begin with V21.i should be able to use LIKE statement and achieve this .
select * from table A where a.column like '^V21%'.
^ - would alway check the first character. Is there a way to check all the first 3 characters ?
Try
WHERE NOT LIKE 'V21%'i cannot use not like in this scenario. the values go into a lookup table and i am joining with the lookup table and i can use only LIKE .
Maybe something like this?
SELECT *
FROM tableA a
JOIN LookUpTable l ON (a.Somecolumn LIKE l.LookupValue AND l.LookUpValue NOT LIKE '^%')
OR (a.Somecolumn NOT LIKE l.LookupValue AND l.LookUpValue LIKE '^%')
August 15, 2018 at 10:30 am
savioceg - Wednesday, August 15, 2018 9:16 AMHi ,I am trying to do a pattern search and my query is generic and it uses LIKE .
The requirement says does not begin with V21.i should be able to use LIKE statement and achieve this .
select * from table A where a.column like '^V21%'.
^ - would alway check the first character. Is there a way to check all the first 3 characters ?
substr(1,3)='V21'
Saravanan
August 15, 2018 at 11:14 am
Hi,
In case you are pulling data from a lookup table, you may dump all those values into a temporary table (say #TmpNotAllowed) and then do a query like this:
SELECT * FROM TableA WHERE SUBSTRING(A.Column, 1, 3) NOT IN(SELECT TmpColumn FROM #TmpNotAllowed)
Please let us know.
August 15, 2018 at 11:36 am
debasis.yours - Wednesday, August 15, 2018 11:14 AMHi,In case you are pulling data from a lookup table, you may dump all those values into a temporary table (say #TmpNotAllowed) and then do a query like this:
SELECT * FROM TableA WHERE SUBSTRING(A.Column, 1, 3) NOT IN(SELECT TmpColumn FROM #TmpNotAllowed)Please let us know.
i am joining with a lookup table with all these configurations. sample configurations are 'V21%S' - means the string starts with V21 and ends with S . there is also a case where it doe snot start with V21. the reason i can use only like is because i am using the join and the join condition is LIKE and i cannot use OR condition with NOT LIKE , this will leade to data duplication. so was trying to find a value that i can populate for not starting with V21 so that i can join with LIKE .
August 15, 2018 at 11:36 am
Luis Cazares - Wednesday, August 15, 2018 10:22 AMsavioceg - Wednesday, August 15, 2018 9:34 AMJonathan AC Roberts - Wednesday, August 15, 2018 9:20 AMsavioceg - Wednesday, August 15, 2018 9:16 AMHi ,I am trying to do a pattern search and my query is generic and it uses LIKE .
The requirement says does not begin with V21.i should be able to use LIKE statement and achieve this .
select * from table A where a.column like '^V21%'.
^ - would alway check the first character. Is there a way to check all the first 3 characters ?
Try
WHERE NOT LIKE 'V21%'i cannot use not like in this scenario. the values go into a lookup table and i am joining with the lookup table and i can use only LIKE .
Maybe something like this?
SELECT *
FROM tableA a
JOIN LookUpTable l ON (a.Somecolumn LIKE l.LookupValue AND l.LookUpValue NOT LIKE '^%')
OR (a.Somecolumn NOT LIKE l.LookupValue AND l.LookUpValue LIKE '^%')
Have not tried this yet, will try and update if it works
August 15, 2018 at 11:50 am
savioceg - Wednesday, August 15, 2018 11:36 AMLuis Cazares - Wednesday, August 15, 2018 10:22 AMsavioceg - Wednesday, August 15, 2018 9:34 AMJonathan AC Roberts - Wednesday, August 15, 2018 9:20 AMsavioceg - Wednesday, August 15, 2018 9:16 AMHi ,I am trying to do a pattern search and my query is generic and it uses LIKE .
The requirement says does not begin with V21.i should be able to use LIKE statement and achieve this .
select * from table A where a.column like '^V21%'.
^ - would alway check the first character. Is there a way to check all the first 3 characters ?
Try
WHERE NOT LIKE 'V21%'i cannot use not like in this scenario. the values go into a lookup table and i am joining with the lookup table and i can use only LIKE .
Maybe something like this?
SELECT *
FROM tableA a
JOIN LookUpTable l ON (a.Somecolumn LIKE l.LookupValue AND l.LookUpValue NOT LIKE '^%')
OR (a.Somecolumn NOT LIKE l.LookupValue AND l.LookUpValue LIKE '^%')Have not tried this yet, will try and update if it works
Can you show us the SQL you are using so we can see how it might be changed?
August 15, 2018 at 12:02 pm
savioceg - Wednesday, August 15, 2018 11:36 AMLuis Cazares - Wednesday, August 15, 2018 10:22 AMMaybe something like this?
SELECT *
FROM tableA a
JOIN LookUpTable l ON (a.Somecolumn LIKE l.LookupValue AND l.LookUpValue NOT LIKE '^%')
OR (a.Somecolumn NOT LIKE l.LookupValue AND l.LookUpValue LIKE '^%')Have not tried this yet, will try and update if it works
I forgot to remove the ^ from the lookup value to use the correct pattern.
August 15, 2018 at 1:49 pm
savioceg - Wednesday, August 15, 2018 11:36 AMdebasis.yours - Wednesday, August 15, 2018 11:14 AMHi,In case you are pulling data from a lookup table, you may dump all those values into a temporary table (say #TmpNotAllowed) and then do a query like this:
SELECT * FROM TableA WHERE SUBSTRING(A.Column, 1, 3) NOT IN(SELECT TmpColumn FROM #TmpNotAllowed)Please let us know.
i am joining with a lookup table with all these configurations. sample configurations are 'V21%S' - means the string starts with V21 and ends with S . there is also a case where it doe snot start with V21. the reason i can use only like is because i am using the join and the join condition is LIKE and i cannot use OR condition with NOT LIKE , this will leade to data duplication. so was trying to find a value that i can populate for not starting with V21 so that i can join with LIKE .
Ignoring the database design issue this kind of thing represents, you still haven't answered the question as to what, exactly, query, you are using, nor have you explained why you can't use NOT LIKE sufficiently, as we can't see what you are doing, exactly, and still don't have the exact circumstances. I'm quite certain there's a query that will work, but you have yet to be sufficiently specific about what your limitation is. If it's just you being worried about data duplication, we can fix that. We just need enough details...
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply