SQL syntax error

Question

Im using Microsoft SQL Server which I think is T-SQL or ANSI SQL.

I want to search a database with a string. The matches that fit the begging of the string should come first then sort alphabetically.

I.e. If the table contains FOO, BAR and RAP

a search for the string 'R' should yield:

RAP
BAR

In that order.

Here is my attempt:

SELECT     Name
FROM       MyTable
WHERE      (Name LIKE '%' + @name + '%')
ORDER BY   (IF(Name LIKE @name + '%',1,0))

The error message is: "must declare scalar variable @name"

Madhivanan · Accepted Answer · 2010-06-08 10:23:09Z

3

declare  @name varchar(10)
set @name='R'
SELECT     Name 
FROM       (select 'foo' as name union select 'RAP' union select 'BAR') MyTable 
WHERE      (Name LIKE '%' +  @name  + '%') 
ORDER BY   charindex(@name ,name)

answered Jun 8, 2010 at 10:23

Madhivanan

13.7k1 gold badge28 silver badges30 bronze badges

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

1 Comment

Robert Over a year ago

I like this solution the best, charindex is more compact and gives a better sort ordering.

TcKs · Accepted Answer · 2010-06-08 10:26:17Z

2

.

DECLARE @name VARCHAR(MAX);
SET @name = 'foo';

SELECT     Name
FROM       MyTable
WHERE      Name LIKE '%' + @name + '%'
ORDER BY   CASE WHEN Name LIKE @name + '%' THEN 1 ELSE 0 END;

edited Jun 8, 2010 at 10:26

TcKs

26.8k11 gold badges72 silver badges110 bronze badges

answered Jun 8, 2010 at 10:19

Dave Markle

98.3k20 gold badges152 silver badges172 bronze badges

1 Comment

Robert Over a year ago

This was what I was trying to do with the IF statement.

devio · Accepted Answer · 2010-06-08 10:55:39Z

1

Other solutions seem to miss the "sort alphabetically" part:

DECLARE    @Search VARCHAR(MAX)
SET        @Search = 'R'

SELECT     0, Name 
FROM       MyTable 
WHERE      Name LIKE @Search + '%'
UNION ALL
SELECT     1, Name
FROM       MyTable
WHERE      Name like '%_' + @Search + '%'
ORDER BY   1, 2

answered Jun 8, 2010 at 10:55

devio

37.3k7 gold badges85 silver badges147 bronze badges

1 Comment

Robert Over a year ago

This is a clever idea, I tried to do it this way to begin with, but I could not get the union working.

Grzegorz Gierlik · Accepted Answer · 2010-06-08 10:57:02Z

1

Seems that you missed variable declaration:

DECALRE @name varchar(50) -- adjust type and length of variable
SET @name = 'phrase' -- for MSSQL 2008 you can do it in one line

edited Jun 8, 2010 at 10:57

answered Jun 8, 2010 at 10:23

Grzegorz Gierlik

11.3k4 gold badges49 silver badges57 bronze badges

Collectives™ on Stack Overflow

SQL syntax error

4 Answers 4

1 Comment

1 Comment

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

1 Comment

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related