I have a table of the following information.
description: adjkfa34kj34kj33j4k3jk4
description: adfkjjkdf34_3434kjkjkj3
description: akjdfkjadfkjadkjfkjadfj
description: 34394394093040930949039
How would I edit the SQL query below to count the number of digits (i.e. [0-9]) in the strings?
select description as num_count
from posts;
One method is to get rid of all the other characters:
select length(regexp_replace(description, '[^0-9]', '', 'g'))
Try this,
CREATE FUNCTION dbo.udf_GetNumeric
(
@strAlphaNumeric VARCHAR(256)
)
RETURNS VARCHAR(MAX)
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 @strAlphaNumeric
END
GO
/*For Example,*/
SELECT LEN(dbo.udf_GetNumeric('') )
SELECT LEN(dbo.udf_GetNumeric('asdf1234a1s2d3f4@@@') )
SELECT LEN(dbo.udf_GetNumeric('123ghdfhdhh456'))
SELECT LEN(dbo.udf_GetNumeric(NULL) )
SELECT LEN(dbo.udf_GetNumeric(description)) AS num_count FROM posts;
sql server syntax, not postgresql
In presto I used the following:
SELECT CARDINALITY(REGEXP_EXTRACT_ALL('a123', '\d')) -- returns 3
