querying a string

Question

Post reply

querying a string

bill.humphrey

SSC Enthusiast

Points: 173
More actions
April 25, 2008 at 4:49 am

#206430

How can I query a varchar(250) field for a specific word:
"Project Managers Book"
"The Project Book"
"The Maintenance Book"
Above are three sperate entries in a db field and I want to select all the entries which contain the word "Project"
"Project Managers Book"
"The Project Book"
Kind Regards
Bill Humphrey

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

MarkusB SSC-Dedicated Points: 37370 More actions · Answer 1

The most simple option would be using LIKE

SELECT myvarcharCol from mytable

WHERE myvarcharCol LIKE '%project%'

Another option would be using the PATINDEX function

SELECT myvarcharCol from mytable

WHERE PATINDEX('project',myvarcharCol) > 0

[font="Verdana"]Markus Bohse[/font]

Grant Fritchey SSC Guru Points: 398909 More actions · Answer 2

Unless they're they're the only things in the field, then you could get away with:

SELECT...

FROM...

WHERE MyField = 'Project Managers Book'

OR MyField = 'The Project Book'

OR MyField = 'The Maintenance Book'

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 3

MarkusB (4/25/2008)
The most simple option would be using LIKE
SELECT myvarcharCol from mytable
WHERE myvarcharCol LIKE '%project%'
Another option would be using the PATINDEX function
SELECT myvarcharCol from mytable
WHERE PATINDEX('project',myvarcharCol) > 0

Try either of those looking for the word "Pro"...

In order to work correctly, you must surround the source and target with the delimiters in the column... in this case, spaces.

[font="Courier New"]SELECT myvarcharCol from mytable

WHERE ' '+myvarcharCol+' ' LIKE '% pro %'[/font]

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

MarkusB SSC-Dedicated Points: 37370 More actions · Answer 4

In order to work correctly, you must surround the source and target with the delimiters in the column... in this case, spaces.
[font="Courier New"]SELECT myvarcharCol from mytable
WHERE ' '+myvarcharCol+' ' LIKE '% pro %'[/font]

Jeff,

you're right if the titles are all like in the examples provided.

But I assumed he might also look for something like "Projectmanagement book".

Also I don't understand why you add the spaces around my columnname ( ' '+myvarcharCol+' ' ). That makes no sense to me.

[font="Verdana"]Markus Bohse[/font]

GSquared SSC Guru Points: 260824 More actions · Answer 5

Jeff Moden (4/25/2008)
MarkusB (4/25/2008)
The most simple option would be using LIKE
SELECT myvarcharCol from mytable
WHERE myvarcharCol LIKE '%project%'
Another option would be using the PATINDEX function
SELECT myvarcharCol from mytable
WHERE PATINDEX('project',myvarcharCol) > 0
Try either of those looking for the word "Pro"...
In order to work correctly, you must surround the source and target with the delimiters in the column... in this case, spaces.
[font="Courier New"]SELECT myvarcharCol from mytable
WHERE ' '+myvarcharCol+' ' LIKE '% pro %'[/font]

The problem with that is it won't find instances at the beginning or end of the string, or with punctuation after the substring.

create table #Test (

ID int identity primary key,

Col varchar(100))

insert into #test (col)

select 'Programming for a pro' union all

select 'Pro Golfing' union all

select 'Program Management' union all

select 'A Real Pro' union all

select 'What would a Pro do?' union all

select 'A Pro, An Amateur, and a Union Member walk into a bar'

select *

from #test

where col like '%[^a-z]pro[^a-z]%'

or col like '%[^a-z]pro'

or col like 'pro[^a-z]%'

That's going to work better than spaces around the "like" statement. Of course, if you want it to include "pros" (plural), or "professional"/"professionals", you can add to the list of Or statements in the Where clause.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 6

MarkusB (4/25/2008)
In order to work correctly, you must surround the source and target with the delimiters in the column... in this case, spaces.
[font="Courier New"]SELECT myvarcharCol from mytable
WHERE ' '+myvarcharCol+' ' LIKE '% pro %'[/font]
Jeff,
you're right if the titles are all like in the examples provided.
But I assumed he might also look for something like "Projectmanagement book".
Also I don't understand why you add the spaces around my columnname ( ' '+myvarcharCol+' ' ). That makes no sense to me.

It keeps you from finding "Project" when you were trying to find "Pro" and allows it to also be found when the first or last word is what is to be found.

create table #Test (

ID int identity primary key,

Col varchar(100))

insert into #test (col)

select 'Programming for a pro' union all

select 'Pro Golfing' union all

select 'Program Management' union all

select 'A Real Pro' union all

select 'What would a Pro do?' union all

select 'A Pro, An Amateur, and a Union Member walk into a bar'

SELECT col from #Test

WHERE ' '+col+' ' LIKE '% pro %'

There is a problem like what GUS stated... if you have plurals or punctuation, mine won't work ... but his does.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 7

GSquared (4/25/2008)
That's going to work better than spaces around the "like" statement. Of course, if you want it to include "pros" (plural), or "professional"/"professionals", you can add to the list of Or statements in the Where clause.

Yep... I agree.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 8

Jeff Moden (4/25/2008)
GSquared (4/25/2008)
That's going to work better than spaces around the "like" statement. Of course, if you want it to include "pros" (plural), or "professional"/"professionals", you can add to the list of Or statements in the Where clause.
Yep... I agree.

Of course - by the time you're onto mapping Pro to Pros, Professional, Professionals....you're into the full text indexing realm, with the related words mappings, etc....

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

GSquared SSC Guru Points: 260824 More actions · Answer 9

Matt Miller (4/25/2008)
Jeff Moden (4/25/2008)
GSquared (4/25/2008)
That's going to work better than spaces around the "like" statement. Of course, if you want it to include "pros" (plural), or "professional"/"professionals", you can add to the list of Or statements in the Where clause.
Yep... I agree.
Of course - by the time you're onto mapping Pro to Pros, Professional, Professionals....you're into the full text indexing realm, with the related words mappings, etc....

Yep. If the case is worth it, that might be the way to go.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon