sqloracle-database

sql extraction of identification numbers


Hi i got the following cases:

i need to extract from each ID format the correct numbers depending on the case

  1. 09N 0720001536 should be cleaned to: 97201536
  2. 08N 0798001682 should be cleaned to: 87981682
  3. 08NT0184001825 should be cleaned to: 81841825
  4. 05NT0702001350 should be cleaned to: 57021350
  5. 08 0732001283 should be cleaned to: 87321283
  6. 00E 0008138007 should be cleaned to: E8138007
  7. 10 0732001283 should be cleaned to: 107321283
  8. 10N 0798001682 should be cleaned to: 107981682
  9. 10NT0702001350 should be cleaned to: 107021350

all cases works except one 6. 00E 0008138007 should be cleaned to: E8138007, right now its cleaning to E813800 so the last number is left, im not sure what im doing wrong in the SP as i see it correctly, any idea of what im missing?

thank you

CREATE OR REPLACE PROCEDURE AGAITAN_PROCESAR_CEDULA (
    p_cedula IN VARCHAR2,
    p_resultado OUT VARCHAR2
) AS
    v_provincia VARCHAR2(2);
    v_bloque3 VARCHAR2(4);
    v_bloque4 VARCHAR2(6);
    v_cedula_limpia VARCHAR2(20);
BEGIN
    -- Paso 1: Limpiar la cédula, manteniendo solo números y la letra 'E'
    v_cedula_limpia := REGEXP_REPLACE(p_cedula, '[^0-9E]', '');

    -- Paso 2: Extraer la provincia
    v_provincia := SUBSTR(v_cedula_limpia, 1, 2);

    -- Paso 3: Extraer los bloques
    v_bloque3 := SUBSTR(v_cedula_limpia, 3, 4);  -- Tercer bloque (dígitos 3 a 6)
    v_bloque4 := SUBSTR(v_cedula_limpia, 7, 6);  -- Cuarto bloque (dígitos 7 a 12)

    -- Paso 4: Manejo de casos específicos
    IF v_cedula_limpia LIKE '00E%' THEN
        -- Caso especial: Si la cédula es de tipo '00E'
        p_resultado := 'E' || LTRIM(v_bloque4, '0');  -- Mantener la letra E y limpiar el bloque 4
    ELSE
        -- Limpiar ceros a la izquierda en la provincia
        v_provincia := LTRIM(v_provincia, '0');

        -- Limpiar ceros a la izquierda en los bloques
        v_bloque3 := LTRIM(v_bloque3, '0');
        v_bloque4 := LTRIM(v_bloque4, '0');

        -- Formar la cédula limpia
        p_resultado := v_provincia || v_bloque3 || v_bloque4;
    END IF;

    -- Asegurarse de que el resultado no esté vacío
    IF p_resultado IS NULL OR p_resultado = '' THEN
        p_resultado := '0';  -- O un mensaje adecuado
    END IF;

EXCEPTION
    WHEN OTHERS THEN
        p_resultado := NULL; -- En caso de error, devolver NULL
END AGAITAN_PROCESAR_CEDULA;
/

case 6. 00E 0008138007 should be cleaned to: E8138007, right now its cleaning to E813800

block 4 debug:

    Block 4 value: 1536
09N 0720001536 Case 1: 97201536
Block 4 value: 1682
08N 0798001682 Case 2: 87981682
Block 4 value: 1825
08NT0184001825 Case 3: 81841825
Block 4 value: 1350
05NT0702001350 Case 4: 57021350
Block 4 value: 1283
08  0732001283 Case 5: 87321283
00E 0008138007 Case 6: E813800
Block 4 value: 1283
10  0732001283 Case 7: 107321283
Block 4 value: 1682
10N 0798001682 Case 8: 107981682
Block 4 value: 1350
10NT0702001350 Cédula limpia 9: 107021350

Statement processed.

Solution

  • Try

    SUBSTR(v_cedula_limpia, 7) 
    

    If substring_length is omitted, then Oracle returns all characters to the end of char. This should help you get more than 6 digits from the 4th block.