Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 sql help required

Author  Topic 

Rahul Raj
Starting Member

41 Posts

Posted - 2015-05-08 : 00:11:31
Hi all,

I have to write one query to exclude data from partial string in the predicate.

Select * from table where col NOT in ('%abc%,%xyz%).

So the data where col value doesn't contain the ABC and xyz string value only should be selected. I am also getting xyz data in the result.

Pls suggest.

Thanks in advance!

Kristen
Test

22859 Posts

Posted - 2015-05-08 : 04:23:59
This perhaps?
Select * from table 
where col NOT LIKE '%abc%'
AND col NOT LIKE '%xyz%'
Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2015-05-08 : 08:46:35
Just for alternate method


where replace(col,'abc','')=col and replace(col,'xyz','')=col

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

Rahul Raj
Starting Member

41 Posts

Posted - 2015-05-09 : 23:01:57
Hi All,

Thanks for your reply!

The parameters ('%abc%','%xyz%') are passed from Unix parameter file and are NOT limited to 2. So if I want to exclude any other text then I can just modify the parameter file.

Is there any other alternative solution. I am thinking of the below

Parameter file
name1 = '%abc%'
name2 = '%xyz%'
name3 = '%qwe%'

parm_file = $1
. $parm_file

export name1
export name2
export name3

Sql Query :

Select * from table where col NOT like $name1
and col NOt like $name2
and col not like $name3

I believe even if name3 is not passed then also this query should work? Please suggest

Thanks in advance



Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2015-05-10 : 03:33:30
If you have potentially one/many then I would put them in a temporary table, and JOIN them or (as the criteria is a NOT LIKE) it might be easier to use EXISTS

SELECT Col1, Col2
FROM MyTable
WHERE NOT EXISTS
(
SELECT *
FROM #TempMatchList
WHERE Col LIKE MatchListValue
)

You could populate the #TempMatchList from a limited string of values using a "splitter function", then the Unix parameter file just needs to contain

%abc%,%xyz%

without the need for any single quote delimiters. (If the file already has the single quotes then the Spliter Function could handle them.)
Go to Top of Page
   

- Advertisement -