Find multiple/different instances

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

Find multiple/different instances

Author

Topic

meef
Posting Yak Master

113 Posts

Posted - 2011-05-11 : 16:40:28

I have to find all occurrences of "Newegg" in a table, but I noticed sometimes it's Newegg and other times it's New Egg so I can't just do a like 'Newegg%'. How can I be sure I get every record?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-05-11 : 17:33:37

WHERE SomeColumn LIKE 'Newegg%' OR SomeColumn LIKE 'New Egg%'

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

Subscribe to my blog

meef
Posting Yak Master

113 Posts

Posted - 2011-05-11 : 21:29:59

I was hoping there was a way to cover all my bases in case there are even more ways it's in the system, is that the only way to do it? I can't really look through all the records to make sure.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-05-11 : 21:46:20

I assume you don't want to pick up if the occurrence is "This is a new type of egg". If that were the case, you could use a condition such as:

SomeColumn like '%new%egg%

SomeColumn like '% new % egg %'

So if you don't want that, you could do this:


replace(SomeColumn,' ','') like '%Newegg%'

But then, can it be like New-egg? Do you want to include that?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-05-11 : 22:55:42

Beware of the performance implications of this. How big is your table?

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

Subscribe to my blog

meef
Posting Yak Master

113 Posts

Posted - 2011-05-12 : 08:34:28

There can be quite a few results, but I'm limiting it to only a few months of data. I'll see how it performs in the future, but I think "%new%egg%" will work for now.

I ran into another problem though. This is my query:

select oci,ost,ozp,dci,dst,dzp,shdt,scac,wtttl,fbttl, conm
from parcelmain
where acct = 'dah' and shdt between '1/1/11' and '4/30/11' and conm like '%new%egg%' or conm like 'magnell%'
order by shdt

Once I added the or conm like 'magnell%' it ignored my date range and is showing me everything. Why did that happen only after adding another WHERE condition?

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-05-12 : 08:48:59

precedence of operators - it has tripped up everyone sometime or other.

where acct = 'dah' and shdt between '1/1/11' and '4/30/11' and ( conm like '%new%egg%' or conm like 'magnell%')

meef
Posting Yak Master

113 Posts

Posted - 2011-05-12 : 08:51:46

Ah, thanks. That didn't even register with me.

Now to get the stupid report showing me what the stored procedure does, it's acting like there's a filter on the report when there isn't and only showing "Magnell" and not "Newegg". Nothing is ever easy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources