Selecting a record from a string containing a value

Question

Post reply

Selecting a record from a string containing a value

Rob Wellard

Newbie

Points: 4
More actions
April 24, 2006 at 4:53 am

#90891

Hi Guys
I have a value passed to me call it "i was born on Friday in may"
I want to select from a table that has a value in it "friday"
so i want to do this
select *
from daysoftheweek
where day = 'i was born on Friday in may'
so if the string contains friday it shows the relavant record
thanks
Rob

Viewing 14 posts - 1 through 13 (of 13 total)

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

paul_richardson SSCarpal Tunnel Points: 4594 More actions · Answer 1

hi, you need the LIKE clause :-

select *

from daysoftheweek

where day like '%Friday%'

and if case is an issue use lower (or upper)

select *

from daysoftheweek

where lower(day) like '%friday%'

Hope this helps

Paul

Rob Wellard Newbie Points: 4 More actions · Answer 2

Hi Paul

Thank you for you reply, I have tried that and it doesnt work

here is the problem

the string i have has a load of stuff it in it, but contains friday

i want to pull the record if it contains friday

my table

id day

1 monday

2 tuesday

3 wednesday

4 thursday

5 friday

6 saturday

select *

from daysoftheweek

where day like 'i was born on Friday afternoon'

hope this helps you understand my issue

thanks

rob

paul_richardson SSCarpal Tunnel Points: 4594 More actions · Answer 3

I see, it's the other way around. maybe case will work. I'll have a play around and let you know

Paul

sushila SSC-Dedicated Points: 35293 More actions · Answer 4

Rob - Don't think you read Paul's "select" carefully enough...he's using wildcards in his "select..."..

LIKE '%Friday%'...you should read this on BOL for a compreshensive grasp!

**ASCII stupid question, get a stupid ANSI !!!**

paul_richardson SSCarpal Tunnel Points: 4594 More actions · Answer 5

hi again, the following works :-

i created a table my_table

(id int, day varchar(10))

and inserted the following rows :-

insert into my_table values(1, 'monday')

insert into my_table values(2, 'tuesday')

insert into my_table values(3, 'wednesday')

insert into my_table values(4, 'thursday')

insert into my_table values(5, 'friday')

insert into my_table values(6, 'saturday')

and then this select returns the required info. you'll obviously need to replace the string with a variable, if possible, or dynamic sql if not.

select *

from my_table

where patindex ("%" + day + "%", lower('i was born on Friday afternoon')) >0

it uses the patindex function (sell BOL for a full explanation) but basically it checks for each day in the string, and if present returns the position. if it's not found it returns 0 (hence the >0)

hope this helps

Paul

paul_richardson SSCarpal Tunnel Points: 4594 More actions · Answer 6

paul_richardson

SSCarpal Tunnel

Points: 4594

April 24, 2006 at 6:08 am

#633747

rob,

see my reply above !

Paul

Rob Wellard Newbie Points: 4 More actions · Answer 7

Thanks Sushila

But no, the string is not friday

the string i am passing in is 'i was born on Friday afternoon'

so i want to pull the record that matches friday

so a wildcard doesnt seem to work.

Thank you once again

sushila SSC-Dedicated Points: 35293 More actions · Answer 8

hmm..it appears that I didn't understand the requirement correctly...(& Paul does.. )..so you can get a string with any day of the week and you need to find matching rows depending on the day of the week passed ?!?!

**ASCII stupid question, get a stupid ANSI !!!**

Rob Wellard Newbie Points: 4 More actions · Answer 9

Hi guys

i did that paul and this is the responce in sql anaylizer

Server: Msg 207, Level 16, State 3, Line 1

Invalid column name '%'.

Server: Msg 207, Level 16, State 1, Line 1

Invalid column name '%'.

paul_richardson SSCarpal Tunnel Points: 4594 More actions · Answer 10

change the " to ', or change your set quoted identifier setting !

vadba SSChampion Points: 11133 More actions · Answer 11

How about this (assuming a case-insensative collation):

select *

from daysoftheweek

where CharIndex(day, 'i was born on Friday in may') > 0

or, if case-sensative

select *

from daysoftheweek

where CharIndex(Lower(day), Lower('i was born on Friday in may')) > 0

Lisa Slater Nicholls Hall of Fame Points: 3454 More actions · Answer 12

You got the error from Paul's statement because he typed "%" rather than '%' (double quotes rather than single). Whether this is going to work or not (I think) depends on the setting of QUOTED_IDENTIFIERS. So just switch to '%' and it should work...

Dan Murray Right there with Babe Points: 779 More actions · Answer 13

I'm not sure if I understand the problem but I think you want to scan a string for a day of the week. I'm not sure if the input is structured, i.e. you provide the text surrounding the Day of the week or unstructured where the user provides all text and you're not sure where the day of the week falls.

One heavy handed method would be to use the case statement, i.e.

select (case when @InStr like '%monday%' then 1 when @InStr like '%tuesday%' then 2...

else -1 end) 'DayOfWeek'

The Else -1 is a Safety in case there is no Day of Week name and should be chaecked for.