CODEWITHSUNDEEP
sqlsql-servert-sql
Robert
Robert

Reputation: 38223

SQL syntax error

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"

Upvotes: 0

Views: 221

Answers (4)

Grzegorz Gierlik
Grzegorz Gierlik

Reputation: 11232

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

Upvotes: 1

devio
devio

Reputation: 37225

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

Upvotes: 1

Dave Markle
Dave Markle

Reputation: 97851

.

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;

Upvotes: 2

Madhivanan
Madhivanan

Reputation: 13700

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)

Upvotes: 3

Related Questions