Like operator with argument “_” not working in SQL 2008 R2 RSS

• • Reply
  

  shashikant20...

  Member

  7 Points

  41 Posts

  Like operator with argument “_” not working in SQL 2008 R2

  Mar 06, 2013 01:27 PM|LINK

  know underscore is used to point number of characters with Like operator but it is not working in SQL Server 2008 R2.

  I am using the following command to display companies name(c_name) whose second alphabet is 'e' from table name ind_comp :

  MY Table Details Are :

  Sno  c_name               addrs                       turnover

  1      pesodent          B-12/7 Kolkata            250

  2     Colgate              127 Mumbai            519

  3       Prestiege        56H Jharkhand          120

  4       Relexo           D-45/12 New Delhi     358

  5       Bajaj             450, Channai              840

  6       TATA            C/90 Bihar                 508

  7      Reliance          Y-90 New Delhi        700

  8      ShaktiBhog         A 120 Punjab        950

  9      Kotak Mahindra   B-12 Mumbai      950

  10      Pepsi India        5/40 Haryana       600

  11      Air India            WZ-90 Goa         1000

  select c_name from ind_comp where c_name LIKE '_e'

  The result it is showing, blank field with just column name.

• • Reply
  

  aaa_78600

  Participant

  826 Points

  173 Posts

  Re: Like operator with argument “_” not working in SQL 2008 R2

  Mar 06, 2013 01:37 PM|LINK

  select c_name from ind_comp where c_name LIKE '_e%'

  Abrar Ahmad Ansari
  • Marked as answer by shashikant2011 on Mar 06, 2013 01:58 PM
• • Reply
  

  imobsuz

  Participant

  1278 Points

  195 Posts

  Re: Like operator with argument “_” not working in SQL 2008 R2

  Mar 06, 2013 01:39 PM|LINK

  Try:

  where SUBSTRING(c_name, 2, 1) = 'e'

  Hope this helps. 

  http://www.imoveisemexposicao.com.br/imobiliarias-em-guarulhos
• • Reply
  

  prabu.raveen...

  Contributor

  5020 Points

  955 Posts

  Re: Like operator with argument “_” not working in SQL 2008 R2

  Mar 06, 2013 01:43 PM|LINK

  Your query will return value when the name field contains two characters and last character is 'e' if not it will not return any values.

  if you want to return all the rows contains secondletter 'e' then try as below,

  select c_name from ind_comp where c_name LIKE '_e%'

• • Reply
  

  Danny117

  Star

  11160 Points

  1932 Posts

  Re: Like operator with argument “_” not working in SQL 2008 R2

  Mar 06, 2013 01:47 PM|LINK

  You're backwards.

  The underscore represents any single character.  % represents number of characters.

  ends with e Like '%E'

  starts with e like 'E%'

  e is second letter like '_E%'

  what you have is a search for a two character string ending with E   like '_E'

   

  Good Luck
  
  Me on linked in
  
  
  
• • Reply
  

  Pbalan.in

  Contributor

  2142 Points

  483 Posts

  Re: Like operator with argument “_” not working in SQL 2008 R2

  Mar 07, 2013 03:44 AM|LINK

  Try this..

  Using the number of underscore to select the companies name like as below...

  Declare @Temp table(sno numeric(18,0),cname nvarchar(50));
  Insert into @Temp values (1,'pesodent');
  Insert into @Temp values (2,'Colgate');
  Insert into @Temp values (3,'Prestiege');
  Insert into @Temp values (4,'Relexo');
  Insert into @Temp values (5,'Reliance');
  Insert into @Temp values (6,'Kotak Mahindra');

  Select * from @Temp where cname like '_e%' --Single underscore
  Select * from @Temp where cname like '__l%' --Double underscore

  Bala