Преобразование NDC из 10 цифр в 11 цифр в sql, когда bulkinsert

Question

Необходимо преобразовать коды NDC из 10 цифр в 11 цифр. в sql server

Answer 1

 UPDATE [dbo].[NDCPackage] SET

[NDCPACKAGECODE]=( CASE CHARINDEX('-', [NDCPACKAGECODE]) КОГДА 5 ТОГДА
ЗАМЕНА ('0'+SUBSTRING([NDCPACKAGECODE],CHARINDEX('', [NDCPACKAGECODE]), LEN([NDCPACKAGECODE])+1), '-', '') КОГДА 6 ЗАТЕМ (CHARINDEX('-', ОБРАТНЫЙ ([NDCPACKAGECODE])))
КОГДА 3 ТОГДА
ЗАМЕНА (ВЛЕВО ([NDCPACKAGECODE], (CHARINDEX('-', [NDCPACKAGECODE])))+'0'+SUBSTRING([NDCPACKAGECODE], CHARINDEX('-', [NDCPACKAGECODE])+1, LEN([NDCP])), '-', '') КОГДА 2 ТОГДА
ЗАМЕНА (STUFF([NDCPACKAGECODE], LEN([NDCPACKAGECODE])- CHARINDEX('-', REVERSE([NDCPACKAGECODE]))) +1, 1, '0'), '-', '') END END)

Answer 2

Если кому-то интересно, это версия MySQL.

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

от наркотиков

Answer 3

Версия Postgres (заполнениеполе встол)

      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
), '-', '')
;