locked
Validate Column Upon Insert RRS feed

  • Question

  • User-1738841732 posted

    My below code works absolutely fine, but I need to validate my Account number on insert
    which should not be more than 9 digits and should NOT have a space or special characters
    how do i achieve that

    WITH mycte AS (
    SELECT [COMPANY_INFO]
    ,TRY_CAST([LOAD_XML] AS XML) company_infoLoad
    FROM [TEMP_DATALOAD]
    )
    INSERT INTO
    [dbo].[COMPANYINFOLOAD]
    ([ACCOUNT_NUMBER]
    ,[DRAFT_NUMBER]
    ,[ACTION_NAME])

    SELECT
    S.a.value('(CompanyInfo/AccountNumber/text())[1]', 'nvarchar(20)') AccountNumber
    ,S.a.value('(CompanyInfo/DraftNumber/text())[1]', 'nchar(10)') DraftNumber
    ,S.a.value('(CompanyInfo/ActionName/text())[1]', 'nchar(1000)') ActionName

    FROM mycte t

    CROSS APPLY t.company_infoLoad.nodes('/.') S(a)

    Wednesday, May 20, 2020 8:02 PM

Answers

  • User452040443 posted

    Try:

    WITH 
        mycte AS 
        (
            SELECT [COMPANY_INFO]
               ,TRY_CAST([LOAD_XML] AS XML) company_infoLoad
            FROM [TEMP_DATALOAD]
        ),
    
        ctenodes as
        (
            SELECT
                S.a.value('(CompanyInfo/AccountNumber/text())[1]', 'nvarchar(20)') AccountNumber
               ,S.a.value('(CompanyInfo/DraftNumber/text())[1]', 'nchar(10)') DraftNumber
               ,S.a.value('(CompanyInfo/ActionName/text())[1]', 'nchar(1000)') ActionName
            FROM mycte t
            CROSS APPLY t.company_infoLoad.nodes('/.') S(a)
        )
    
    INSERT INTO
    [dbo].[COMPANYINFOLOAD]
    ([ACCOUNT_NUMBER]
    ,[DRAFT_NUMBER]
    ,[ACTION_NAME])
    
    SELECT
        AccountNumber
       ,DraftNumber
       ,ActionName
    FROM ctenodes
    WHERE
        LEN(AccountNumber) <= 9 AND
        AccountNumber NOT LIKE '%[^0-9A-z]%'

    Hope this help

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, May 21, 2020 2:05 AM