CODEWITHSUNDEEP
sqlsql-server
David Tunnell
David Tunnell

Reputation: 7542

WHERE clause based on part of a VARCHAR column

I have a query where I need to search the numerical part of a string in SQL Server.

enter image description here

In the number column above needs to be searchable as a variable in the query.

Wildcards does not work:

SELECT PK_Story 
FROM Story 
WHERE ProductId = @productParam 
  AND Number LIKE '%' + @numberParam + '%';

because this would also return 132 and 232 for example.

So how can I search for a specific number after the '-'. As you can see I can't do charindex because of the variable prefix length.

Upvotes: 0

Views: 792

Answers (3)

A_Sk
A_Sk

Reputation: 4630

what about this

declare @My_Number as varchar(50)='8'

SELECT PK_Story 
FROM Story
WHERE ProductId = @productParam 
AND substring(Number, charindex('-', Number) +1, len(Number)) like 
@My_Number +'%'

Or, if want equal

SELECT PK_Story 
    FROM Story
    WHERE ProductId = @productParam 
    AND substring(Number, charindex('-', Number) +1, len(Number)) =
    @My_Numbe

Upvotes: 0

Pavel Gatnar
Pavel Gatnar

Reputation: 4053

What about LIKE '%-' + @numberParam?

Upvotes: 1

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49260

You can use substring and charindex combination to get the result.

SELECT PK_Story 
FROM Story
WHERE ProductId = @productParam 
AND @numberParam like 
'%' + case when charindex('-', Number) > 0 
           then substring(Number, charindex('-', Number) +1, len(Number)) + '%'
      else Number 
      end + '%'

Upvotes: 1

Related Questions