T-SQL for getting string function

Question

Post reply

T-SQL for getting string function

mpalaparthi

Say Hey Kid

Points: 688
More actions
October 18, 2010 at 3:49 pm

#230632

Hello SQL Experts,
I have a question.... I have 3 tables (table1, table 2 and table3)
table1: table2
ID meat id type
1 beef pork 1 beef
2 pork chicken 2 chicken
3 pork chicken beef 3 beef
I have table3.
I am trying to write a t-sql querythat: first it will go to table1 and check for meat column, if the column has beef then it will go to table2 and then pick the ID corrosponding to beef and then add those 2 columns (table2.ID and meat) in table3. if the column has pork, chicken and beef this process should repeate 3 times and add the 3 rows in table3.
My question is how to get the data in meat column and check if it has pork and chicken (or) chicken, pork and beef etc..... is there any string function that I can use in T-SQL. (There is a space between 2 meats types in the meat column).
Please give me some T-SQL syantx for this.
Thank you all so much
--Sree

Viewing 8 posts - 1 through 7 (of 7 total)

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

mpalaparthi Say Hey Kid Points: 688 More actions · Answer 1

sorry for messing up the table structure, here is the tables list:

table1:

ID | meat

1 | beef pork

2 | pork chicken

3 | pork chicken beef

table2

id type

1 beef

2 chicken

3 beef

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 2

We're going to want to see expected results if we're going to try to figure this out for you, unfortunately. It's a little odd. Also, if you can take a look at the first link in my signature, it will help you lay out the structure for us to help you.

Additionally, I assume you've tried to work through this already, please post your attempts so far.

EDIT: This screams either homework question or horrendous data layout. I'd like to know which we're dealing with.

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

irobertson Hall of Fame Points: 3695 More actions · Answer 3

How about:

select * into bbq

from table2

/*

result

-------------------

nom nom nom nom nom

(1 row(s) affected)

*/

Or, seriously, try looking at charindex and patindex in BOL - they should help you <to finish that homework>

😀

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

mpalaparthi (10/18/2010)
Hello SQL Experts,
I have a question.... I have 3 tables (table1, table 2 and table3)
table1: table2
ID meat id type
1 beef pork 1 beef
2 pork chicken 2 chicken
3 pork chicken beef 3 beef
I have table3.
I am trying to write a t-sql querythat: first it will go to table1 and check for meat column, if the column has beef then it will go to table2 and then pick the ID corrosponding to beef and then add those 2 columns (table2.ID and meat) in table3. if the column has pork, chicken and beef this process should repeate 3 times and add the 3 rows in table3.
My question is how to get the data in meat column and check if it has pork and chicken (or) chicken, pork and beef etc..... is there any string function that I can use in T-SQL. (There is a space between 2 meats types in the meat column).
Please give me some T-SQL syantx for this.
Thank you all so much
--Sree

Can you please list the structure and content of table 3, results? This will help define the question. Thanks.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Muhammad Tariq Ten Centuries Points: 1020 More actions · Answer 5

try below code:

select t2.id,t1.meat

from table2 t2

cross join table1 t1

Where (case when t1.meat like '%'+t2.type+'%' then 1 else 0 end) = 1

order by t1.meat

Tariq
master your setup, master yourself.
http://mssqlsolutions.blogspot.com

GSquared SSC Guru Points: 260824 More actions · Answer 6

You can Join by using a Like statement. That's probably what your teacher is looking for.

On the other hand, the "step through" makes it sound like the assignment might be about using cursors. What's the chapter about?

- 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: 1000539 More actions · Answer 7

mpalaparthi (10/18/2010)
Hello SQL Experts,
I have a question.... I have 3 tables (table1, table 2 and table3)
table1: table2
ID meat id type
1 beef pork 1 beef
2 pork chicken 2 chicken
3 pork chicken beef 3 beef
I have table3.
I am trying to write a t-sql querythat: first it will go to table1 and check for meat column, if the column has beef then it will go to table2 and then pick the ID corrosponding to beef and then add those 2 columns (table2.ID and meat) in table3. if the column has pork, chicken and beef this process should repeate 3 times and add the 3 rows in table3.
My question is how to get the data in meat column and check if it has pork and chicken (or) chicken, pork and beef etc..... is there any string function that I can use in T-SQL. (There is a space between 2 meats types in the meat column).
Please give me some T-SQL syantx for this.
Thank you all so much
--Sree

Read the article at the first link in my signature line below. Try posting a bit of data and table structure that way and see the difference in how folks help you. 😉

--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)
Intro to Tally Tables and Functions