Validate Column Upon Insert

May 20 2020 3:00 PM
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 achive 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)

Brought to you by:

Answers (1)