Help with LIKE clause - 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

Help with LIKE clause

Author

Topic

and7ew
Starting Member

2 Posts

Posted - 2009-06-23 : 15:04:42

Hi all,

Right now, I have the following tables

Student Table
studentId(int) sName(nvarchar) sCourses(nvarchar)
1000 Andrew Edwards 1000;1001
1001 Tommy Jones 1000

Courses
courseId(int) courseName(nvarchar)
1000 Programming 101
1001 System Design 101

When I try to execute the following SELECT statement, I only get records with 1000 even though I specified it to get all fields with the '1000' in it...

SELECT s.studentId, s.sName, s.event
FROM tblStudent s INNER JOIN tblCourses
ON s.event = CAST(c.eventId AS nvarchar)
WHERE (s.event LIKE '%1000%')

What am I doing wrong? any ideas?

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-06-23 : 15:29:00

Is sCourse in your tables Event in your query? If so, how would 1000 ever equal 1000; 1001? This line will not let that happen ON s.event = CAST(c.eventId AS nvarchar)
You could try something like:

SELECT s.studentId, s.sName, s.event
FROM tblStudent s INNER JOIN tblCourses 
ON s.event LIKE '%' + CAST(c.eventId AS nvarchar) + '%'

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-23 : 15:47:10

ON ';' + s.event + ';' LIKE '%;' + CAST(c.eventId AS nvarchar(40)) + ';%'

E 12°55'05.63"
N 56°04'39.26"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-06-24 : 02:03:32

Here is why you should use length
http://sqlblogcasts.com/blogs/madhivanan/archive/2007/12/04/column-length-and-data-length.aspx

Madhivanan

Failing to plan is Planning to fail

and7ew
Starting Member

2 Posts

Posted - 2009-06-24 : 09:04:30

Thanks Lamprey! That Query worked like a charm =)

One question, right now with the query I get the following...

Andrew Smith Programming 101
Andrew Smith System Design 101
Andrew Smith Ethics 102

how do you combine the following records into one so its something like this

Andrew Smith Programming 101, System Design 101, Ethics 102

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-24 : 09:06:50

quote:
Originally posted by and7ew

Thanks Lamprey! That Query worked like a charm =)

Not quite. See the difference when you have both "10" and "100" for ID.
See post made 06/23/2009 : 15:47:10 to deal with those.

See topic here
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=81254
how to concatenate strings.

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources