CODEWITHSUNDEEP
sql
ramana
ramana

Reputation: 31

sql like command

Using Like operator, how to retrieve a name which is having exact 100 characters

note: I don't want to use 100 underscores _ ex: 

select ename from emp where ename like '_____________';

it should not like above is there any other way

Upvotes: 3

Views: 94

Answers (5)

Umair Awan
Umair Awan

Reputation: 11

You can use following:

WHERE column_name REGEXP [a-z0-9]{100}

Upvotes: 0

Nishanth Matha
Nishanth Matha

Reputation: 6081

    select ename from emp where LEN(ename)=100 and ename like '%_%';

LEN is used for length of value and % before and after character means any string which contains atleast one _

for more that ore equal to 100 

select ename from emp where LEN(ename)>=100 and ename like '%_%';

Upvotes: 0

Jun Rikson
Jun Rikson

Reputation: 1884

I'm not quite sure what DBMS you using, this is should work under mysql :

select ename from emp where ename like concat('%', REPEAT('_',100),'%');

Above condition is where string have 100 UNDERSCORE whether in beginning, middle or end of string. For condition where string exactly have 100 UNDERSCORE :

select ename from emp where ename = REPEAT('_',100);

Upvotes: 0

jarlh
jarlh

Reputation: 44696

Many dbms products support the REPEAT function (not ANSI however...)

select ename from emp where ename like repeat('_',100)

Or use CHAR_LENGTH:

select ename from emp where CHAR_LENGTH(ename) = 100

Upvotes: 0

Mudassir Hasan
Mudassir Hasan

Reputation: 28741

For exactly 100 characters , do 

select ename from emp where LEN(ename)=100

For greater than equal to 100 characters

select ename from emp where LEN(ename) >= 100

Upvotes: 2

Related Questions