Scalar function throws error while using in SQL

Arghadeep Dey 0
Viewed 43

My question is:

Write a query to display user name and password. Password should be generated by concatenating first two characters of user name , length of the user name and last three numbers in the phone number and give an alias name as USER_PASSWORD. Sort the results based on the user name in descending order.

select
    name,
    concat(substring(name, 1, 2), cast(len(name) as varchar), cast(right(phno, 3) as varchar)) as USER_PASSWORD 
from 
    users
order by 
    name desc;

I get this error:

cast(len(name) as varchar),

  • ERROR at line 5: ORA-00906: missing left parenthesis

Thanks

enter image description here

oracle sql
2 Answers

Concat() is limited to two arguments in Oracle. Use || instead.

with my_data as (
  select 'abcdefg' as name, 12345 as phno from dual
  )
select
name,
  substr(name, 1, 2) || 
  length(name) ||  
  substr(to_char(phno),-3) as user_password
from my_data

| NAME    | USER_PASSWORD |
| --------|---------------|
| abcdefg | ab7345        |

fiddle

Isolated0

You have five issues:

  1. CONCAT only takes two arguments so you either need CONCAT(a, CONCAT(b, c)) or use the || string concatenation operator a || b || c
  2. CAST requires the data type and length CAST(a AS VARCHAR2(10))
  3. SUBSTRING is not an Oracle function, you want SUBSTR;
  4. LEN is not an Oracle function, you want LENGTH;
  5. RIGHT is not an Oracle function, your want SUBSTR with a negative index.
SELECT name,
       concat(
         substr(name, 1, 2),
         concat(
           cast(length(name) as varchar2(10)),
           cast(SUBSTR(phno, -3) as varchar2(10))
         )
       ) as USER_PASSWORD 
from   users
order by name desc;

However, you do not need to explicitly use CAST as you can use an implicit conversion between data types:

SELECT name,
       substr(name, 1, 2) || length(name) || SUBSTR(phno, -3) as USER_PASSWORD 
from   users
order by name desc;

Which, for the sample data:

CREATE TABLE users (name, phno) AS
  SELECT 'Benny', '0123111' FROM DUAL UNION ALL
  SELECT 'Betty', '4567111' FROM DUAL UNION ALL
  SELECT 'Beryl', '2222111' FROM DUAL;

Both output:

NAME USER_PASSWORD
Betty Be5111
Beryl Be5111
Benny Be5111

fiddle

Which leads to the final point, don't generate obvious passwords; generate random or pseudo-random passwords. Then don't store them as plain text; instead store them as a salted-hash.

MT00
Related