Sql String Function

Question

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

For what database? @ for variables is supported by SQL Server and MySQL. — OMG Ponies
– OMG Ponies, Commented Feb 16, 2011 at 5:49
Can you make your question more clearer? Something like create the result of table... — Crimsonland
– Crimsonland, Commented Feb 16, 2011 at 5:54

Brettski · Accepted Answer · 2011-02-16 06:55:23Z

1

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.

edited Feb 16, 2011 at 6:55

answered Feb 16, 2011 at 5:59

Brettski

20.3k17 gold badges78 silver badges101 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Suneesh.sh Over a year ago

Hi , thanks....but problem is it will modify the actual string...eg..if the string "i have deleted the user 'Test user'"..this will produce and output -"i have deleted the user ''test' user'"...here the name 'Test' changes to test....i think u understand my problem

Brettski Over a year ago

The is the problem you deal with when you work with case insensitive collation.

Sachin Shanbhag · Accepted Answer · 2011-02-16 06:10:39Z

1

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  tag, I think you may have to use regex to replace the input string with  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.

edited Feb 16, 2011 at 6:10

answered Feb 16, 2011 at 5:54

Sachin Shanbhag

55.7k11 gold badges92 silver badges103 bronze badges

4 Comments

OMG Ponies Over a year ago

Where's the text updating? Oracle doesn't support @ in a variable either.

Suneesh.sh Over a year ago

thanks for reply,Sql server 2005 - but my problem is ,i got the all comments by using the s"elect comment from table where comment like @input" statements,but i want to modify the substring( like @input) in the string as i explained in frist post

Sachin Shanbhag Over a year ago

@OMG Ponies - Aaah.. The question is edited after my answer. Sorry, did not see the edit. The tag was not visible in first version of question. Hence my answer.

Brettski Over a year ago

This will only do an exact match to @input even though you used LIKE. You need to add a wild card(s).

Collectives™ on Stack Overflow

Sql String Function

2 Answers 2

2 Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related