SELECT SqlCommand

Question

I know this is a simple question, but I can't get it to work.

This is my query in my SqlCommand:

SELECT * FROM zipcode  WHERE city LIKE @prefixtext + '%' ;

I only want 10 results, every other answer suggests

SELECT TOP 10 * FROM zipcode  WHERE city LIKE @prefixtext + '%' ;
SELECT * FROM zipcode  WHERE city LIKE @prefixtext + '%'  LIMIT 10 ;

both do not work

I'm likely wrong on this since I'm not very familiar with SQL Server, but I don't think that + is concatenation. — Corbin
– Corbin, Commented Jun 7, 2012 at 2:55
Is zipcode really your table name, or the name of the column you want to retrieve? Basic query format is SELECT column_name (or * for all columns) ` FROM table_name WHERE ...` — bfavaretto
– bfavaretto, Commented Jun 7, 2012 at 2:55
TOP 10 is SQL Server specific. LIMIT 10 works on MySQL (maybe also Oracle?). Which DB are you using? What error or incorrect result do you get? — Eric J.
– Eric J., Commented Jun 7, 2012 at 2:58
SELECT TOP 10 * FROM zipcode WHERE city LIKE @prefixtext + '%' would definitely work in MSSQL, limit 10 is not MSSQL keyword. What is the error here? — fenix2222
– fenix2222, Commented Jun 7, 2012 at 3:01
Could you show the code where you populate the SqlCommand object? — rikitikitik
– rikitikitik, Commented Jun 7, 2012 at 3:11

Phil · Accepted Answer · 2012-06-07 04:49:31Z

2

I believe these are all correct.

Oracle:

select * from zipcode where city like @prefixtext + '%' and rownum <=10

SQL Server/Sybase:

select top 10 * from zipcode where city like @prefixtext + '%'

DB2/PostgreSQL:

select * from zipcode where city like @prefixtext || '%' fetch first 10 rows only

MySQL:

select * from zipcode where city like @prefixtext + '%' limit 10

answered Jun 7, 2012 at 4:49

Phil

4976 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Gonzalo.- · Accepted Answer · 2012-06-07 09:53:57Z

0

declare @like varchar(50)

set @like = @prefixtext + '%';

SELECT TOP 10 * FROM zipcode  WHERE city LIKE @like

edited Jun 7, 2012 at 9:53

Gonzalo.-

12.7k5 gold badges53 silver badges84 bronze badges

answered Jun 7, 2012 at 4:41

vijay

585 bronze badges

Comments

j0k · Accepted Answer · 2012-09-11 10:11:07Z

-1

Select * from zipcode where city like @prefixtext + '%'

edited Sep 11, 2012 at 10:11

j0k

22.8k28 gold badges81 silver badges90 bronze badges

answered Sep 11, 2012 at 9:57

vivek

193 bronze badges

1 Comment

Ian Ringrose Over a year ago

Does not keep to " I only want 10 results"

Collectives™ on Stack Overflow

SELECT SqlCommand

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related