无法将值NULL插入到列'col‘中- SQL Server

Question

我的一个专栏中有这样的数据:我有大约10万条这样的记录。

            tele
            ---------

            C12345
            75784329899(c)
            75678934729(cell)
            ygasd786782399
            guisahkl#57812897
            5476783-6779834

我写了这段代码来删除所有字母，只保留数字。运行得很好。

            create TABLE #TEMP
            (
            id [int] NOT NULL,
            tele [char](14),
            col [bigint] NOT NULL DEFAULT ''
            )
            ;with cte as (select top (100) N = row_number() over

            (order by @@spid) from sys.all_columns),

            data as(

            select id,tele,col

                from table1` 
                cross apply (
                    select (select C + ''
                        from (select N, substring( tele,N,1) C from cte where N <= datalength(tele)) [1]
                        where C between '0' and '9'
                        order by N
                        for xml path(''))
                        ) P(col)

                    where p.col is not null 

-插入到临时表中

            INSERT INTO #TEMP (id,tele,col) 
            SELECT id,tele,cast(col as bigint) 
            FROM data 



            tele
            ---------

            12345
            75784329899
            75678934729
            786782399
            57812897
            54767836779834  

然而，现在我想保留所有的单元编号，只删除垃圾(或未知字符)，我的结果应该是这样的：

                tele                           col
            -----------------------------------------------------

            C12345                          C12345
            75784329899(c)                  75784329899(c)
            75678934729(cell)               75678934729(cell)
            ygasd786782399                  786782399
            guisahkl#57812897               57812897
            5476783-6779834                 54767836779834

我修改了下面的代码：

            create TABLE #TEMP
            (
            id [int] NOT NULL,
            tele [char](14),
            col [bigint] NOT NULL DEFAULT ''
            )
            ;with cte as (select top (100) N = row_number() over

            (order by @@spid) from sys.all_columns),

            data as(

            select id,tele,col

                from table1` 
                cross apply (
                    select (select C + ''
                        from (select N, substring( tele,N,1) C from cte where N <= datalength(tele)) [1]
                        where C between '0' and '9'
                        order by N
                        for xml path(''))
                        ) P(col)

                    where p.col is not null AND

                      col NOT LIKE '%(CELL)%' 
                      OR col NOT LIKE  '%CELL%' OR col NOT LIKE '%(C)%'
                      OR col NOT LIKE '%C%' OR col NOT LIKE  '%MOBILE%'
                      OR col NOT LIKE  '%X%' OR col NOT LIKE '%EXT%'
                      OR col NOT LIKE '%XT%' OR col NOT LIKE '%EX%' 
                      OR col NOT LIKE '%/%' OR col NOT LIKE '%,%'
                               )

-插入到临时表中

            INSERT INTO #TEMP (id,tele,col) 
            SELECT id,tele,cast(col as bigint) 
            FROM data 

然而，它向我显示了这个错误：

        Cannot insert the value NULL into column 'col', table 'tempdb.dbo.#TEMP_______________________________________________________________________________________________________________000000000125';
        column does not allow nulls. INSERT fails.

我该如何纠正这个问题呢？请帮帮忙。谢谢。

Answer 1

删除列col的临时表的CREATE语句中的NOT NULL约束。

Answer 2

1. 从表创建中删除NOT NULL约束。
2. 如果您不想允许NULL，则将INSERT更改为如下所示的NULL

查询：

  INSERT INTO #TEMP (id,tele,col) 
  SELECT id,tele,ISNULL(cast(col as bigint), '')
  FROM data 

Answer 3

你能像这样插入到#TEMP (id，tele，col) SELECT id，tele，CAST( ISNULL(col，'') as BigInt) FROM data吗