Get Help:Ask a Question in our Forums|Report a Bug|More Help Resources
Last post Mar 07, 2013 03:44 AM by Pbalan.in
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.
Mar 06, 2013 01:37 PM|LINK
select c_name from ind_comp where c_name LIKE '_e%'
Mar 06, 2013 01:39 PM|LINK
where SUBSTRING(c_name, 2, 1) = 'e'
Hope this helps.
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,
Mar 06, 2013 01:47 PM|LINK
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'
Mar 07, 2013 03:44 AM|LINK
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