SQL query to get 6 digits in the output value?-CodePudding

I have a query that gives the output like

select position_id from per_all_people_F

Position_id
FRT567
GFT890
GFT000876
ABC00046

How do i make sure that after first 3 letters, the numbers have to be 6 digit.

Say for example :

FRT567 should be FRT000567.
GFT890 should be GFT000890
or ABC00046 should be ABC000046

How can i tweak my query to accomodate this change?

CodePudding user response：

You can use

SELECT SUBSTR(position_id,1,3)||LPAD(REGEXP_REPLACE(position_id,'\D '),6,'0')
  FROM per_all_people_F

assuming your data format is similar to the presented samples at all.

first piece : ordinary substring extraction for first three letters
second piece : only digits are extracted from the string by using REGEXP_REPLACE(), then zeroes are left padded to the string upto six characters
then concatenate the pieces by double pipe characters

CodePudding user response：

Using only standard string functions (no regular expressions) - split the string after the initial three letters and concatenate the required number of zeros in the middle. This will work correctly even when there are no digits to begin with (the entire input string is just the three letters).

with
  t (position_id) as (
    select 'FRT567'    from dual union all
    select 'GFT890'    from dual union all
    select 'GFT000876' from dual union all
    select 'ABC00046'  from dual union all
    select 'XQY'       from dual
  )
select position_id,
       substr(position_id, 1, 3) || rpad('0', 9 - length(position_id), '0') ||
       substr(position_id, 4) as valid_position_id
from   t;

POSITION_ID  VALID_POSITION_ID   
------------ --------------------
FRT567       FRT000567           
GFT890       GFT000890           
GFT000876    GFT000876           
ABC00046     ABC000046           
XQY          XQY000000

CodePudding user response：

You can use simple string functions and find the first 3 characters using SUBSTR(position_id, 1, 3) and then concatenate || it with the remaining characters left-padded with zeroes to a length of 6 using LPAD(SUBSTR(position_id, 4), 6, '0'). If you can have 3-characters strings then you can use COALESCE to make sure there are always 6 digits:

SELECT position_id,
       SUBSTR(position_id, 1, 3) || LPAD(SUBSTR(position_id, 4), 6, '0')
         AS expanded_position_id,

       -- Optional version for short strings
       SUBSTR(position_id, 1, 3)
       || COALESCE(LPAD(SUBSTR(position_id, 4), 6, '0'), '000000')
         AS expanded_position_id2
FROM   per_all_people_F

Which, for the sample data:

CREATE TABLE per_all_people_F (position_id) as
  SELECT 'FRT567'    FROM DUAL UNION ALL
  SELECT 'GFT890'    FROM DUAL UNION ALL
  SELECT 'GFT000876' FROM DUAL UNION ALL
  SELECT 'ABC00046'  FROM DUAL UNION ALL
  SELECT 'ABC'  FROM DUAL;

Outputs:

POSITION_ID EXPANDED_POSITION_ID EXPANDED_POSITION_ID2

FRT567 FRT000567 FRT000567

GFT890 GFT000890 GFT000890

GFT000876 GFT000876 GFT000876

ABC00046 ABC000046 ABC000046

ABC ABC ABC000000

POSITION_ID	EXPANDED_POSITION_ID	EXPANDED_POSITION_ID2
FRT567	FRT000567	FRT000567
GFT890	GFT000890	GFT000890
GFT000876	GFT000876	GFT000876
ABC00046	ABC000046	ABC000046
ABC	ABC	ABC000000

db<>fiddle here