Converting NDCs from 10-digits to 11 digits in sql when bulkinsert

Question

need to convert NDCs codes from 10-digits to 11 digits. in sql server

Answer 1

[NDCPACKAGECODE]=(
  CASE CHARINDEX('-', [NDCPACKAGECODE]) 
      WHEN 5 THEN  
             REPLACE('0'+SUBSTRING([NDCPACKAGECODE],CHARINDEX('', [NDCPACKAGECODE]), LEN([NDCPACKAGECODE])+1), '-', '')
      WHEN 6 THEN 
      CASE (CHARINDEX('-', REVERSE([NDCPACKAGECODE])))       
        WHEN 3 THEN  
            REPLACE(LEFT([NDCPACKAGECODE], (CHARINDEX('-', [NDCPACKAGECODE])))+'0'+SUBSTRING([NDCPACKAGECODE], CHARINDEX('-', [NDCPACKAGECODE])+1, LEN([NDCPACKAGECODE])), '-', '') 
        WHEN 2 THEN     
    REPLACE(STUFF([NDCPACKAGECODE], LEN([NDCPACKAGECODE])- CHARINDEX('-', REVERSE([NDCPACKAGECODE]))+1, 1, '0'), '-', '')
    END
    END
    )

Answer 2

In case anyone is curious, this is the MySQL version of this.

select 
ndc_package_code,
replace((
    case locate('-', ndc_package_code)
        when 5 then
            concat(0, ndc_package_code)
        when 6 then
            case (locate('-', reverse(ndc_package_code)))
                when 3 then
                    concat(
                        left(ndc_package_code, locate('-', ndc_package_code)),
                        concat(0, substring(ndc_package_code, locate('-', ndc_package_code) + 1, length(ndc_package_code)))
                    )
                when 2 then
                    concat(left(ndc_package_code, length(ndc_package_code) - 1), '0', right(ndc_package_code, 1))
            end
    end
), '-', '') as normalized_code

from drugs

Answer 3

A Postgres version (populating a normalized_code field in a packages table)

UPDATE packages
    SET normalized_code = REPLACE((
    CASE POSITION('-' IN ndc_package_code)
        when 5 then
            CONCAT(0, ndc_package_code)
        WHEN 6 THEN
            CASE (POSITION('-' IN REVERSE(ndc_package_code)))
                WHEN 3 THEN
                    CONCAT(
                        LEFT(ndc_package_code, POSITION('-' IN ndc_package_code)),
                        CONCAT(0, SUBSTRING(ndc_package_code, POSITION('-' IN ndc_package_code) + 1, LENGTH(ndc_package_code)))
                    )
                WHEN 2 THEN
                    CONCAT(LEFT(ndc_package_code, LENGTH(ndc_package_code) - 1), '0', RIGHT(ndc_package_code, 1))
            END
    END
), '-', '')
;