Link to home
Create AccountLog in
Avatar of pardeshirahul
pardeshirahul

asked on

how to remove the space in the data to get the value

I have a query

 SELECT p1.person_id
           FROM per_all_people_f p1
          WHERE UPPER (trim(p1.last_name) || ', ' || trim(p1.first_name)) =
                   UPPER ('LEJOLIVET               , HUBERT')
                AND p1.business_group_id = 1747
                AND p1.current_employee_flag = 'Y'
                AND p1.effective_start_date =
                       (SELECT MAX (pp.effective_start_date)
                          FROM per_all_people_f pp
                         WHERE pp.person_id = p1.person_id
                               AND pp.business_group_id =
                                      p1.business_group_id);

the data which i am getting is 'LEJOLIVET               , HUBERT'
now when i am comparing it with UPPER (trim(p1.last_name) || ', ' || trim(p1.first_name)) =

i am not getting the value
individual last_name 'LEJOLIVET'  and first_name 'HUBERT'
is in the table
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

I don't think I'm understanding the problem.

Are you saying that UPPER (trim(p1.last_name) || ', ' || trim(p1.first_name))  is returning values with spaces in it?

If not, try:

WHERE UPPER (trim(p1.last_name) || ', ' || trim(p1.first_name)) =
                   UPPER ('LEJOLIVET,HUBERT')
ASKER CERTIFIED SOLUTION
Avatar of awking00
awking00
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Better: why concat them?

WHERE UPPER (trim(p1.last_name)) = 'LEJOLIVET' and upper(trim(p1.first_name)) = ('HUBERT')