CODEWITHSUNDEEP
sqloracle-databaseoracle11g
muiz
muiz

Reputation: 23

UpperCase first name and last name find from table if there is first name and last name is more than one character

I want to fetch first and last name from table which are in Uppercase letter but name should be more than one character in the result

if suppose name is WILLIAM and so it should be fetched but if name is W it should not be

So far I have achieved this result, this is fetching uppercase name but it also fetching name which has only one character which should not be happening 

SELECT  pp.pid, mp.fnm, mp.lnm
FROM mmd_all_people mp join
     people pp
     ON mp.contid = pp.contid join
     entities es
     ON pp.pid = es.pid join
     entnums nums
     On es.pid=nums.pid
WHERE regexp_like (mp.fnm, '^[[:upper:]] + $') or
      regexp_like (mp.LNM, '^[[:upper:]] + $') AND
      ES.MMD = 'A' AND
      ((nums.TOTASS) > 50000)

Upvotes: 1

Views: 180

Answers (4)

muiz
muiz

Reputation: 23

select pp.pid , mp.fnm,mp.lnm from  mmd_all_people mp join people pp on mp.contid=pp.contid where ( regexp_like (mp.fnm, '^[[:upper:]]+$') or
 regexp_like(mp.LNM, '^[[:upper:]]+$') ) and ( length(mp.fnm) >2 and length(mp.lnm) >2)

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269873

I'm not sure what you mean by "name", given that you have two name columns in the query. If you want each to have at least two characters, then modify the regular expressions:

WHERE (regexp_like(mp.fnm, '^[[:upper:]]{2,}$') or
       regexp_like(mp.LNM, '^[[:upper:]]{2,}$')
      ) AND
      . . .

I suspect that your real problem is the lack of parentheses around the first two conditions.

Upvotes: 0

Sunil Khatri
Sunil Khatri

Reputation: 389

You can use length(). Add this line in your where clause :

and length(mp.fnm) > 1

Upvotes: 1

Littlefoot
Littlefoot

Reputation: 142743

If you want to return names whose first name length is larger than 1, then add another condition:

and length(mp.fnm) > 1

Upvotes: 0

Related Questions