We help IT Professionals succeed at work.

return value based on substr

chalie001
chalie001 asked
on
162 Views
Last Modified: 2018-02-02
hi got the following query

select empacc from employeee
              where empid = '18'
              and  substr(empaccid,1,3) = '0960825'
              and  substr(empaccid,4,4)  = '0960825'
                  
                  the user enter value like this
                  
                  18-096-0825 in database i got
                  
                  empacc  empid  empaccid
                  2840     18    0960825
                  
                  
                  i what to return 2840
Comment
Watch Question

Developer
CERTIFIED EXPERT
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
Nitin SontakkeDeveloper
CERTIFIED EXPERT

Commented:
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)

Open in new window

Author

Commented:
What if I only enter  '180960825'

Author

Commented:
Both query still refining null
Nitin SontakkeDeveloper
CERTIFIED EXPERT

Commented:
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!

Author

Commented:
The first query return value
Nitin SontakkeDeveloper
CERTIFIED EXPERT

Commented:
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.
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
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.
Mark GeerlingsDatabase Administrator
CERTIFIED EXPERT

Commented:
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.

Author

Commented:
correct
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.