I want to write an SQL query using the LIKE keyword. It should search the first character or the starting character of my column with the search parameter.
Is there a specific syntax for doing this?
views:
210answers:
4
+6
A:
Is this what you're looking for
SELECT *
FROM yourtable
WHERE yourcolumn LIKE 'X%'
This will find all rows where yourcolumn starts with the letter X.
To find all that ends with X:
...
WHERE yourcolumn LIKE '%X'
...and contains an X...
...
WHERE yourcolumn LIKE '%X%'
Lasse V. Karlsen
2009-07-08 08:57:18
+3
A:
Try
select * from table where column like 'c%'
where 'c' is the character you want
Ezekiel Rage
2009-07-08 08:57:42
+1
A:
Here's two examples...
SELECT FirstName
FROM tblCustomer
WHERE FirstName LIKE 'B%'
The % sign is the wildcard, so this would return all results where the First Name starts with B.
This might be more efficient though...
SELECT FirstName
FROM tblCustomer
WHERE LEFT(FirstName, 1) = 'B'
Sohnee
2009-07-08 08:58:25
Actually the second version will almost certainly be less efficient as it cannot take advantage of an index on the FirstName column.
Matt Howells
2009-07-08 09:01:00
"This *might* be more efficient though..." - we don't know anything about the database or whether there is an index of the field being searched. The execution plan is king.
Sohnee
2009-07-09 06:57:33
Test on random table: LIKE: 2 mins 38 seconds - PATINDEX: 2 mins 39 seconds - LEFT / LEN: 2 mins 20 seconds. Another table would supply different results.
Sohnee
2009-07-09 07:01:47
+1
A:
For a parameterized query, which you seem to have ("search the first character [...] with the search parameter"), use this:
SELECT *
FROM yourtable
WHERE yourcolumn LIKE ? + '%'
Tomalak
2009-07-08 09:13:31