SQL - Add new column with outputs as values

Question

Just wondering how I might go about adding the ouputted results as a new column to an exsisting table. What I'm tryng to do is extract the date from a string which is in another column. I have the below code to do this:

Code

CREATE FUNCTION dbo.udf_GetNumeric
(
  @strAlphaNumeric VARCHAR(256)
)
RETURNS VARCHAR(256)
AS
BEGIN
  DECLARE @intAlpha INT
  SET @intAlpha = PATINDEX('%[^0-9]%', @strAlphaNumeric)
  BEGIN
    WHILE @intAlpha > 0
    BEGIN
      SET @strAlphaNumeric = STUFF(@strAlphaNumeric, @intAlpha, 1, '' )
      SET @intAlpha = PATINDEX('%[^0-9]%', @strAlphaNumeric )
    END
  END
  RETURN ISNULL(@strAlphaNumeric,0)
END
GO
Now use the function as


SELECT dbo.udf_GetNumeric(column_name) 
from table_name

The issue is that I want the result to be placed in a new column in an exsisting table. I have tried the below code but no luck.

ALTER TABLE [Data_Cube_Data].[dbo].[DB_Test]
ADD reportDated nvarchar NULL;



insert into [DB].[dbo].[DB_Test](reportDate)
SELECT 
         (SELECT dbo.udf_GetNumeric(FileNamewithDate) from [DB].[dbo].[DB_Test])

are you sure about the mysql tag? This looks ore like SQL SERVER — nbk
– nbk, Commented Feb 4, 2022 at 12:10
I would strongly suggest rewriting that function; it's a multi-line scalar function (which are known to perform poorly at times) and it has a WHILE (which do perform terribly). If you are writing a function, use an inline table value function. 2019 does support inlining of scalar functions, but the above won't be inlined. — Thom A
– Thom A ♦, Commented Feb 4, 2022 at 12:14
Also you define reportDated as an nvarchar(1), but your function returns varchar(256); that's a significant data type mismatch. — Thom A
– Thom A ♦, Commented Feb 4, 2022 at 12:15

Aaron Bertrand · Accepted Answer · 2022-02-04 12:40:54Z

1

The syntax should be an UPDATE, not an INSERT, because you want to update existing rows, not insert new ones:

UPDATE Data_Cube_Data.dbo.DB_Test -- you don't need square bracket noise
  SET reportDate = dbo.udf_GetNumeric(FileNamewithDate);

But yeah, I agree with the others, the function looks like the result of a "how can I make this object the least efficient thing in my entire database?" contest. Here's a better alternative:

-- better, set-based TVF with no while loop
CREATE FUNCTION dbo.tvf_GetNumeric
(@strAlphaNumeric varchar(256))
RETURNS TABLE
AS
  RETURN 
  (
    WITH cte(n) AS 
    (
      SELECT TOP (256) n = ROW_NUMBER() OVER (ORDER BY @@SPID)
      FROM   sys.all_objects
    )
    SELECT output = COALESCE(STRING_AGG(
           SUBSTRING(@strAlphaNumeric, n, 1), '')
           WITHIN GROUP (ORDER BY n), '')
    FROM   cte
    WHERE  SUBSTRING(@strAlphaNumeric, n, 1) LIKE '%[0-9]%'
  );

Then the query is:

UPDATE t
  SET t.reportDate = tvf.output
  FROM dbo.DB_Test AS t
  CROSS APPLY dbo.tvf_GetNumeric(t.FileNamewithDate) AS tvf;

Example db<>fiddle that shows this has the same behavior as your existing function.

edited Feb 4, 2022 at 12:40

answered Feb 4, 2022 at 12:19

Aaron Bertrand

282k37 gold badges469 silver badges468 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

PythonBeginner Over a year ago

I am receiving the following error "Invalid column name 'reportDated'"

Aaron Bertrand Over a year ago

@PythonBeginner (1) your question has ADD reportDated to the table but then reportDate in the insert, so I'm not sure which one you really did. (2) you need a GO between the alter table and the update, otherwise SQL Server will try to parse the column reference before the alter happens.

Thom A · Accepted Answer · 2022-02-04 12:39:35Z

The function

As i mentioned in the comments, I would strongly suggest rewriting the function, it'll perform terribly. Multi-line table value function can perform poorly, and you also have a WHILE which will perform awfully. SQL is a set based language, and so you should be using set based methods.

There are a couple of alternatives though:

Inlinable Scalar Function

SQL Server 2019 can inline function, so you could inline the above. I do, however, assume that your value can only contain the characters A-z and 0-9. if it can contain other characters, such as periods (.), commas (,), quotes (") or even white space ( ), or your not on 2019 then don't use this:

CREATE OR ALTER FUNCTION dbo.udf_GetNumeric (@strAlphaNumeric varchar(256))
RETURNS varchar(256) AS 
BEGIN
    RETURN TRY_CONVERT(int,REPLACE(TRANSLATE(LOWER(@strAlphaNumeric),'abcdefghigclmnopqrstuvwxyz',REPLICATE('|',26)),'|',''));
END;
GO

SELECT dbo.udf_GetNumeric('abs132hjsdf');

The LOWER is there in case you are using a case sensitive collation.

Inline Table Value Function

This is the better solution in my mind, and doesn't have the caveats of the above.

It uses a Tally to split the data into individual characters, and then only reaggregate the characters that are a digit. Note that I assume you are using SQL Server 2017+ here:

DROP FUNCTION udf_GetNumeric; --Need to drop as it's a scalar function at the moment
GO
CREATE OR ALTER FUNCTION dbo.udf_GetNumeric (@strAlphaNumeric varchar(256))
RETURNS table AS 
RETURN
    WITH N AS (
        SELECT N
        FROM (VALUES(NULL),(NULL),(NULL),(NULL)) N(N)),
    Tally AS(
        SELECT TOP (LEN(@strAlphaNumeric))
               ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS I
        FROM N N1, N N2,  N N3, N N4)
    SELECT STRING_AGG(CASE WHEN V.C LIKE '[0-9]' THEN V.C END,'') WITHIN GROUP (ORDER BY T.I) AS strNumeric
    FROM Tally T
         CROSS APPLY (VALUES(SUBSTRING(@strAlphaNumeric,T.I,1)))V(C);
GO

SELECT *
FROM dbo.udf_GetNumeric('abs132hjsdf');

Your table

You define reportDated as nvarchar; this means nvarchar(1). Your function, however, returns a varchar(256); this will rarely fit in an nvarchar(1).

Define the column properly:

ALTER TABLE [dbo].[DB_Test] ADD reportDated varchar(256) NULL;

If you've already created the column then do the following:

ALTER TABLE [dbo].[DB_Test] ALTER COLUMN reportDated varchar(256) NULL;

I note, however, that the column is called "dated", which implies a date value, but it's a (n)varchar; that sounds like a flaw.

Updating the column

Use an UPDATE statement. Depending on the solution this would one of the following:

--Scalar function
UPDATE [dbo].[DB_Test]
SET reportDated = dbo.udf_GetNumeric(FileNamewithDate);

--Table Value Function
UPDATE DBT
SET reportDated = GN.strNumeric
FROM [dbo].[DB_Test] DBT
     CROSS APPLY dbo.udf_GetNumeric(FileNamewithDate);

Collectives™ on Stack Overflow

SQL - Add new column with outputs as values

2 Answers 2

2 Comments

The function

Inlinable Scalar Function

Inline Table Value Function

Your table

Updating the column

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

The function

Inlinable Scalar Function

Inline Table Value Function

Your table

Updating the column

Comments

Your Answer

Sign up or log in

Post as a guest

Related