Contains Function in Sql Server.

Posted by Nismeh under Sql Server on 1/1/2014 | Points: 10 | Views : 1833 | Status : [Member] | Replies : 5

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

Hi,

I am creating one cursor in SQL Server. Now I want to check that one of it variable contains specific text or not. How can I check it..
For Ex.
declare @var varchar(50) = 'testimg test test'

Now i want to check that this @var contains 'img' or not?
In this ex it contains ..

IT KNOWLEDGE IS APPLIED KNOWLEDGE
So Just Do It

Reply | Reply with Attachment

Alert Moderator

Responses

Posted by: kgovindarao523-21772 on: 1/1/2014 [Member] [MVP] Bronze | Points: 25

0	Hi, Try like this declare @var varchar(50) = 'testimg test test' SELECt @var WHERE @var like '%img%' Thank you, Govind Nismeh, if this helps please login to Mark As Answer. \| Alert Moderator

Posted by: kgovindarao523-21772 on: 1/1/2014 [Member] [MVP] Bronze | Points: 25

0	Hi, this can also be help you. DECLARE @NUM INT SET @NUM=0 DECLARE @var varchar(50) = 'testimg test test' SELECT @NUM=LEN(@var) WHERE @var like '%img%' IF(@NUM=0) BEGIN PRINT 'CONTAINS FAIL' END ELSE BEGIN PRINT 'CONTAINS SUCCESS' END Thank you, Govind Nismeh, if this helps please login to Mark As Answer. \| Alert Moderator

Hi,

this can also be help you.

DECLARE @NUM INT
SET @NUM=0
DECLARE @var varchar(50) = 'testimg test test'
SELECT @NUM=LEN(@var) WHERE @var like '%img%'
IF(@NUM=0)
  BEGIN
     PRINT 'CONTAINS FAIL'
  END
ELSE
   BEGIN
       PRINT 'CONTAINS SUCCESS'
   END

Thank you,
Govind

Nismeh, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Nismeh on: 1/1/2014 [Member] Starter | Points: 25

0	Sorry Buddy It will not fulfill my requirement. I want to set if condition using that function. IT KNOWLEDGE IS APPLIED KNOWLEDGE So Just Do It Nismeh, if this helps please login to Mark As Answer. \| Alert Moderator

Posted by: vishalneeraj-24503 on: 1/1/2014 [Member] [MVP] Platinum | Points: 25

0	Hi,you can Use Charindex in-built sql server function. It is just like IndexOf in Dot Net. For Example:- if CHARINDEX('test',@var) > 0 begin --do something end For reference:- http://sandeep-tada.blogspot.in/2013/08/sql-server-check-if-string-contains.html http://robbamforth.wordpress.com/2009/07/28/sql-check-if-one-string-contains-another-string-charindex/ http://www.aspdotnet-suresh.com/2013/02/sql-server-check-if-string-contains.html Nismeh, if this helps please login to Mark As Answer. \| Alert Moderator

Hi,you can Use Charindex in-built sql server function.
It is just like IndexOf in Dot Net.

For Example:-

if CHARINDEX('test',@var) > 0

begin

    --do something

end

For reference:-

http://sandeep-tada.blogspot.in/2013/08/sql-server-check-if-string-contains.html http://robbamforth.wordpress.com/2009/07/28/sql-check-if-one-string-contains-another-string-charindex/ http://www.aspdotnet-suresh.com/2013/02/sql-server-check-if-string-contains.html

Nismeh, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Bandi on: 1/3/2014 [Member] [MVP] Platinum | Points: 25

0	Sample code: declare @cur cursor SET @cur = CURSOR FOR select last_name, department_id from study..employees where LAST_NAME LIKE '%kin%'; open @cur; declare @last varchar(30), @deptID int FETCH @cur INTO @last, @deptID IF ( @Last LIKE '%king%') BEGIN SELECT @last END ELSE BEGIN SELECT @deptID END close @cur Mark This Response as Answer -- Chandu http://www.dotnetfunda.com/images/dnfmvp.gif Nismeh, if this helps please login to Mark As Answer. \| Alert Moderator

Sample code:

declare @cur cursor 
SET @cur = CURSOR FOR select last_name, department_id from study..employees  where LAST_NAME LIKE '%kin%';
open @cur;
declare @last varchar(30), @deptID int
FETCH @cur INTO @last, @deptID

IF ( @Last LIKE '%king%')
BEGIN
	SELECT @last
END
ELSE 
BEGIN
	SELECT @deptID
END
close @cur

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Nismeh, if this helps please login to Mark As Answer. | Alert Moderator

Latest Posts