SEARCHING FOR A TEXT INSIDE AN STRING

[Raul2005]

I need some help to:

ID USERNAME MESSage
1 nrj hello(nrj)there is a..
2 pqt hola(nrj) today we had
3 nrj Try one more(nrj)time


I need the following resul

ID USERNAME MESSage STATUS
1 nrj hello(nrj)there SENT
2 pqt hola(nrj) today we RECEIVED
3 nrj Try one more(nrj) SENT

As you can see I need to look inside the message string for the value I have in field USERNAME Ineed to compare and if inside the message string I found a value like the USERNAME I need to display another field with tthe status set to SENT in other case it will be se to Received.

I will appreciate any help

Raul

 

[gmmastros]

This is untested, but should solve your problem

Select ID,
       UserName,
       Message,
       Case When CharIndex(UserName, Message) > 0
            Then 'Sent'
            Else 'Received'
            End As Status
From   Table

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[Raul2005]

Thanks gmmastros.
However the result is returning "Received" for everyone I believe there is a problem with the parentesis around.

IT SHOULD RETURN

ID USERNAME MESSage STATUS
1 nrj hello(nrj)there SENT
2 pqt hola(nrj) today we RECEIVED
3 nrj Try one more(nrj) SENT

IT IS RETURNING

ID USERNAME MESSage STATUS
1 nrj hello(nrj)there RECEIVED
2 pqt hola(nrj) today we RECEIVED
3 nrj Try one more(nrj) RECEIVED


Thanks

 

[Raul2005]

I solved like this>>>>>>

Select ID,
UserName,
Message,
Case When CharIndex('('+rtrim(UserName)+')', Message) > 0
Then 'Sent'
Else 'Received'
End As Status
From ATEST