How to slect data from table ?

Question

Post reply

How to slect data from table ?

gouri92

SSC Veteran

Points: 259
More actions
December 25, 2014 at 8:22 pm

#397808

Hi,
I am designing a webpge using asp.net with sql server 2005
in back end there is table with followding columns
username,name,phone1,phone2,age and some others columns are there for storing user's details.
I would like to select in such a way so that i enter
more than one user name seperated with commas in textbox
in selection result i get all the phone numbers associated with that usernames.
For example
Usernames NamePhone1Phone2
abc@yahoo.comAbhi93344592879334459288
def@yahoo.comSony9935199351
efg@yahoo.comTony98351983519835198351
Select Phone1,Phone2 from tblregistration where usernames=abc@yahoo.com,def@yahoo.com,efg@yahoo.com
In result i like all the phone numbers
93344592879334459288
9935199351
98351983519835198351
Please help me.

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

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

Shafat Husain SSCrazy Points: 2754 More actions · Answer 1

Dear Gauri,

You will have to use a table valued split function to cater to this requirement.

A split function will take up the string as 'val1,val2,val3....' and a delimiter which will be "," in your case and provide you with ready to use value, which can be query using a simple select statement and passed in a sub query in your original query.

Here is a link of similar issue posted in a different forum.

http://dba.stackexchange.com/questions/21078/t-sql-table-valued-function-to-split-a-column-on-commas

Hope it Helps...!!

Kindly mark it as solution if it helped resolve your issue

Regards,
Shafat Husain
🙂
And Your Lord Never Forgets...!! (64:19 -- Quran)

serg-52 SSCrazy Eights Points: 9913 More actions · Answer 2

Try

Select Phone1,Phone2 from tblregistration

where 'abc@yahoo.com,def@yahoo.com,efg@yahoo.com' like '%'+Usernames+'%'

sandeep rawat Ten Centuries Points: 1317 More actions · Answer 3

Hi

go throw below link.

http://www.codeproject.com/Tips/584680/Using-comma-separated-value-parameter-strings-in-S

Jeff Moden SSC Guru Points: 1004403 More actions · Answer 4

Shafat Husain (12/25/2014)
Dear Gauri,
You will have to use a table valued split function to cater to this requirement.
A split function will take up the string as 'val1,val2,val3....' and a delimiter which will be "," in your case and provide you with ready to use value, which can be query using a simple select statement and passed in a sub query in your original query.
Here is a link of similar issue posted in a different forum.
http://dba.stackexchange.com/questions/21078/t-sql-table-valued-function-to-split-a-column-on-commas
Hope it Helps...!!
Kindly mark it as solution if it helped resolve your issue

If it has a WHILE loop in it, two things are wrong...

1) It's a scalar function that will be about 7 times slower than if it were an iTVF. Please see the following article for proof of that.

http://www.sqlservercentral.com/articles/T-SQL/91724/

2) It's going to be relatively slow. Actually, a lot slow. Please see the following article for proof of that.

http://www.sqlservercentral.com/articles/Tally+Table/72993/

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

sandeep rawat (12/26/2014)

Hi

go throw below link.

http://www.codeproject.com/Tips/584680/Using-comma-separated-value-parameter-strings-in-S%5B/quote%5D

If it has a WHILE loop in it, two things are wrong...

1) It's a scalar function that will be about 7 times slower than if it were an iTVF. Please see the following article for proof of that.

http://www.sqlservercentral.com/articles/T-SQL/91724/

2) It's going to be relatively slow. Actually, a lot slow. Please see the following article for proof of that.

http://www.sqlservercentral.com/articles/Tally+Table/72993/

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

serg-52 (12/26/2014)
Try
Select Phone1,Phone2 from tblregistration
where 'abc@yahoo.com,def@yahoo.com,efg@yahoo.com' like '%'+Usernames+'%'

That could end up being a pretty bad performance problem because it's not SARGable. Guaranteed table scan, if I'm reading that correctly.

--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)

serg-52 SSCrazy Eights Points: 9913 More actions · Answer 7

Jeff Moden (12/26/2014)
serg-52 (12/26/2014)
Try
Select Phone1,Phone2 from tblregistration
where 'abc@yahoo.com,def@yahoo.com,efg@yahoo.com' like '%'+Usernames+'%'
That could end up being a pretty bad performance problem because it's not SARGable. Guaranteed , if I'm reading that correctly.

It will, if the site will manage to attract millions of users. And being it the case it definitley will need a bit more sofisticated frontend which will be able to parse comma separated list before sending it to SQL. If number of users is under 50000 i see no big perfomance problems with table scan.

Jeff Moden SSC Guru Points: 1004403 More actions · Answer 8

serg-52 (12/26/2014)
Jeff Moden (12/26/2014)
serg-52 (12/26/2014)
Try
Select Phone1,Phone2 from tblregistration
where 'abc@yahoo.com,def@yahoo.com,efg@yahoo.com' like '%'+Usernames+'%'
That could end up being a pretty bad performance problem because it's not SARGable. Guaranteed , if I'm reading that correctly.
It will, if the site will manage to attract millions of users. And being it the case it definitley will need a bit more sofisticated frontend which will be able to parse comma separated list before sending it to SQL. If number of users is under 50000 i see no big perfomance problems with table scan.

Or until someone in a hurry runs across the code and uses it for something much bigger. 😉

It just doesn't take that much longer to do it right the first time. If it's the second time, it takes no extra time to do it right. There just has to be a first time.

--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)

MMartin1 One Orange Chip Points: 27799 More actions · Answer 9

Create a table valued function that returns the delimited string as a column in a table. Join whatever table you are working on to this table (valued function) on the key columns.

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

Jeff Moden SSC Guru Points: 1004403 More actions · Answer 10

MMartin1 (12/26/2014)
Create a table valued function that returns the delimited string as a column in a table. Join whatever table you are working on to this table (valued function) on the key columns.

Most people don't know how to do that first part with performance in mind. If you can limit it to 8K bytes, here's an article on a fast T-SQL-only solution. If you need more than that, the article also contains a simple SQLCLR solution that Paul White wrote.

http://www.sqlservercentral.com/articles/Tally+Table/72993/

--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)