51

I have this table in SQL Server 2012:

Id INT 
DomainName NVARCHAR(150)

And the table have these DomainName values

  1. google.com
  2. microsoft.com
  3. othersite.com

And this value:

mail.othersite.com

and I need to select the rows where the string ends with the column value, for this value I need to get the row no.3 othersite.com

It's something like this: 

DomainName Like '%value'

but in reverse ...

'value' Like %DomainName
Aldwoni
  • 1,168
  • 10
  • 24
Mario
  • 13,941
  • 20
  • 54
  • 110

4 Answers4

91

You can use such query:

SELECT * FROM TABLE1
WHERE 'value' LIKE '%' + DomainName
dotnetom
  • 24,551
  • 9
  • 51
  • 54
  • 7
    Note that on other databases (eg Oracle) you'll need to use the string concatenation operator, because '+' will give you an invalid number error. E.g: `WHERE 'value' LIKE '%' || DomainName` – Tydaeus Aug 02 '16 at 15:58
0

It works on mysql server. [ LIKE '%' || value ] not works great because when value start with numbers this like not return true.

SELECT * FROM TABLE1 WHERE DomainName like CONCAT('%', value)
Talha Dilber
  • 117
  • 1
  • 3
-1

In Oracle you can use REGEXP_LIKE string function for string start/ends with search. This function 1st argument is an expression/source string and 2nd argument is a pattern/search string. Use dollar $ for a ends with value and ^ for starts with value. For example

select * from TABLE1 where REGEXP_LIKE(DomainName, :value ||'$');

If you have a OR condition between different values, then use following pipe | syntax.

select * from TABLE1 where REGEXP_LIKE(DomainName, 'value1$|value2$|value2$');

Refer this link for more info on REGEXP_LIKE string function

Tarun Kumar
  • 2,918
  • 3
  • 15
  • 17
-1

one possible answer could be

SELECT patient_id,first_name from patients where first_name like 'S%s'
shyam_gupta
  • 309
  • 2
  • 11