CODEWITHSUNDEEP
sqlsql-serverstring
Pete
Pete

Reputation: 469

How can I find record in SQL that matches the start of a string?

I have a table of prefixes like so:

 Primary Key | Value

      1      | 0012
      2      | 0013
      3      | 0014
      4      | 0015

And wondered how I could write a query that would return the primary key of the prefix of the code I pass through.

For example if I had the value 00135648954 I would like to pass that through to a query to return 2 because 00135648954 starts with 0013.

I have tried writing a few LIKE queries without success.

I looked into a CONTAIN query which wouldn't run because the table is not full-text indexed (also i haven't had any experience with CONTAINS so I didn't want to try too much)

Upvotes: 0

Views: 36

Answers (2)

GMB
GMB

Reputation: 222432

Just use like:

select primary_key
from mytable 
where @myval like concat(value, '%')

Where @myval represents the value for which you want to get the primary key.

If you want something that is null-safe (ie, that will not match if @myval is null), then you can use + as concatenation operator instead:

select primary_key
from mytable 
where @myval like value + '%'

Upvotes: 3

juergen d
juergen d

Reputation: 204756

select id
from your_table
where @parameter like value + '%'

Upvotes: 0

Related Questions