sql help required - SQL Server Forums

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%'

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

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

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources