Sql String Function

Need help to do a simple task in sql string in my table there is a field comment(user comments).

In my procedure, I am passing a parameter @input. I want to check whether the string contains the string @input, and if it contains @input then add some style to the substring(that same as @input).

eg: if the Comment string like - "i have added the user Test user" and

@input - "tes"

I want the output as

i have added the user <span style="color:red">Tes</span>t 开发者_如何学编程user

Using T-SQL (Microsoft SQL)

SELECT REPLACE(comment, @input, '<span style="color:red">' + SUBSTRING(comment, PATINDEX('%' + @input + '%', comment), len(@input)) + '</span>') as cmt
FROM thetable
WHERE comment LIKE '%' + @input + '%'

EDIT: OK Even more ugly and still untested, but should work for you. Now we are replaceing the exact text from the string back into the replace.

UPDATE: I just tested this code and it works as you need it to. It finds Tes in the string using 'tes' and outputs Tes as it is in the original string.

select comment from table where comment like '%' + @input + '%'

Irrespective of the RDBMS, there is LIKE in other RDBMS(SQLserver, Oracle, Mysql). Specifically my answer is assuming your DB is Sql server since I noticed the use of @

EDIT: To get the <span> tag, I think you may have to use regex to replace the input string with <span> tag in the output result. Better would be to do it in the underlying code instead of getting results from SQL query with format. In SQL server you can use REPLACE function too.