Can someone help me understand the syntax here? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

Can someone help me understand the syntax here?

Author

Topic

SlinginParts
Starting Member

18 Posts

Posted - 2012-06-25 : 14:08:28

[code]
SELECT DISTINCT Attribute, Value
FROM ATR
WHERE value like 'SC___'
or value like 'SC__'
and attribute like '&part&'
and not attribute like '%brand%'
and not attribute like '&type&'
and not attribute like '&model&'
and not attribute like '&make&'
order by value asc[/code]

Results

Attribute Value
Other Part Number SC099
Manufacturer Part Number SC288
Model SC300
Model Scamp
Brand Scion
Interchange Part Number Scion
Make Scion
Part Brand Scion
Model Scout

Obviously I have some misunderstanding of the correct use of these functions. I have tried the not both after the and and before the like, neither worked.

I'm trying to find an easy way to exude non-relevant results. I'm sure I need to be using some sort of variation of an if then statement but I'm very new to this with about 8 hours under my belt, so any help is appreciated.

SlinginParts
Starting Member

18 Posts

Posted - 2012-06-25 : 14:14:13

And to clarify, the results

Other Part Number SC099
Manufacturer Part Number SC288

Are what I'm trying to get and

Model SC300
Model Scamp
Brand Scion
Interchange Part Number Scion
Make Scion
Part Brand Scion
Model Scout

I am trying to scrub.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2012-06-25 : 14:15:20

You need to consistently use % when using LIKE. You've got some without it and some using &.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

xhostx
Constraint Violating Yak Guru

277 Posts

Posted - 2012-06-25 : 14:27:03

quote:
Originally posted by SlinginParts

SELECT DISTINCT Attribute, Value
FROM ATR
WHERE value like 'SC___'
or value like 'SC__'
and attribute like '&part&'
and not attribute like '%brand%'
and not attribute like '&type&'
and not attribute like '&model&'
and not attribute like '&make&'
order by value asc

Results

Attribute Value
Other Part Number SC099
Manufacturer Part Number SC288
Model SC300
Model Scamp
Brand Scion
Interchange Part Number Scion
Make Scion
Part Brand Scion
Model Scout

Obviously I have some misunderstanding of the correct use of these functions. I have tried the not both after the and and before the like, neither worked.

I'm trying to find an easy way to exude non-relevant results. I'm sure I need to be using some sort of variation of an if then statement but I'm very new to this with about 8 hours under my belt, so any help is appreciated.

Distinct ==> will only bring UNIQUE values from your table (if there's any duplicates i will not appear part of result)
Underscores (_) will act like any wild card to replace any character in the third, fourth or fifth position in your case...

Percentage (%) usually represents a string of characters (it could be any number, character, or nothing sometimes '').

Order by Columns name ASC= ASCENDING.

\Good luck

kml

SlinginParts
Starting Member

18 Posts

Posted - 2012-06-25 : 16:09:42

OK thank you guys. First of all, the use of & and % is embarrassing.

Here is what solved it for me. I'm sure this is a terrible way to do it, but until I get better here's what worked for anyone that wants to learn from my mistakes!

SELECT DISTINCT Attribute, Value
FROM ATR
WHERE
attribute like '%part%'
and (value like 'SC__' or value like 'SC___')
/*value like 'SC-___' or value like 'SC-__' or */
and not (attribute like '%brand%' or attribute like '%type' or attribute like '%model%' or attribute like '%make%')
and not value like '%scion%'
order by value asc

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2012-06-25 : 16:13:43

For the ones where you aren't using %, use = instead: value = 'SC__' or value = 'SC___'

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

SlinginParts
Starting Member

18 Posts

Posted - 2012-06-25 : 18:03:48

Thank you. I'm working on a monster query that probably goes beyond the scope of newby posts so I'm going to put together more documentation and post it to the appropriate forum. I see you're all over the place here so I'm sure I'll see you in there.

Thanks a lot! This is a lot to take in

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources