return value based on substr

But where is the parameter of whatever user has entered? Let me give you sql server code ...modify it to match oracle one.

declare @empaccid varchar(20) = '18-096-0825'
select empacc from employeee
              where empid = '18'
              and  substr(empaccid, 1, 3) = substr(@empaccid, 4, 3)
              and  substr(empaccid, 4, 4)  = substr(@empaccid, 8, 4)

Select allOpen in new window

What if I only enter  '180960825'

Both query still refining null

No. Then we wouldn't know where to substring from. Better approach is that you get rid of hyphen (-) and then send value as parameter and then modify from to chars.

Can you paste Oracle code, if you have it, please!

The first query return value

It will because we comparing values to themselves. Not sure if that is what is expected.

I thought other part of equation will come from user input. That's why i gave you second query and asked you to modify it so as to be suitable for Oracle PL/SQL environment.

What is different about this question than the previous substr question:
https://www.experts-exchange.com/questions/29008238/subtr-returning-incorrect-value.html

It seems pretty similar.

In the query you provided, it will never return anything.
and  substr(empaccid,1,3) = '0960825'
and  substr(empaccid,4,4)  = '0960825'

Neither of those SUBSTR calls will EVER return anything equal to '0960825'.

First SUBSTR call returns 3 characters and the second returns 4 characters.

I agree with slightwv, this query in Oracle will never return anything!

select empacc from employeee
               where empid = '18'
               and  substr(empaccid,1,3) = '0960825'
               and  substr(empaccid,4,4)  = '0960825'

If you change that to this, it can return something:
select empacc from employeee
               where empid = '18'
               and  substr(empaccid,1,3) = '096'
               and  substr(empaccid,4,4)  = '0825'

Or, if you change it to this, it can return something:
select empacc from employeee
               where empid = '18'
               and  substr(empaccid,1,3) = substr('0960825',1,3)
               and  substr(empaccid,4,4)  = substr('0960825',4,4)

Either of those variations can work if you have a record where the empaccid = (or starts with): '0960825'.

But, be aware that when you use "substr" on a database column value in the "where" clause of a query, your performance may not be very fast.

correct