Tukukkk

There's data I'm trying to retrieve using a SELECT statement which is input by various people in regards to an amount in grams. As there doesnt seem to be a standard here are some of the ways people have written it:

• 200g


• 0.2KG


• 0.200kg


• 0.2


• 0.2g(e)


• 2KG


• .222

I currently have this function below that returns all numerical characters into an NVARCHAR which I could then CONVERT into a decimal to use in calculations.

ALTER FUNCTION [dbo].[fn_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

This does work fine in some cases but, for an example, any data that's written like "0.2..." turns into a "2" which whilst working with grams and Kilograms isn't helpful.

I need it to output either to a DECIMAL or NVARCHAR and to keep the decimal place location so a 0.2 stays as a 0.2 and a 0.200 stays as such .

(letters are only ever at the start or end never inbetween the numbers, it does sometimes start with a .)

You can find some char that is not used, and replace .0 with it. Then put it back:

 BEGIN



DECLARE @intAlpha INT



    SET @strAlphaNumeric = REPLACE(@strAlphaNumeric, '.0', '#');

    SET @strAlphaNumeric = REPLACE(@strAlphaNumeric, '0.', '##');

    SET @strAlphaNumeric = REPLACE(@strAlphaNumeric, '.', '###');



    SET @intAlpha = PATINDEX('%[^0-9#]%', @strAlphaNumeric)



    WHILE @intAlpha > 0

    BEGIN

        SET @strAlphaNumeric = STUFF(@strAlphaNumeric, @intAlpha, 1, '' )

        SET @intAlpha = PATINDEX('%[^0-9#]%', @strAlphaNumeric )

    END



    SET @strAlphaNumeric = REPLACE(@strAlphaNumeric, '###', '.')

    SET @strAlphaNumeric = REPLACE(@strAlphaNumeric, '##', '0.');

    SET @strAlphaNumeric = REPLACE(@strAlphaNumeric, '#', '.0');



SELECT ISNULL(@strAlphaNumeric,0)

END

or just add the . like this:

PATINDEX('%[^0-9.]%', @strAlphaNumeric)

but you will have issue if you have string like this: te....st 5.0 kg, so it will be better just to replace .0 with something special.

Note, in the example I am replacing with # but you can replace with five of them in order to make the value more unique - #####, for example.

String parsing in T-SQL is always challenging. But with this situation, SUBSTRING might be easier.

You mentioned this: letters are only ever at the start or end never inbetween the numbers

So basically we just need to find where the number starts and ends in the string.

Have a look at this:

DECLARE @String NVARCHAR(100)



SET @String = 'This is k.g. or g it''s .0456 not g'



SELECT PATINDEX('%[0-9 ].%[0-9]%', @String ) --Where is the first occurance of a number

SELECT PATINDEX('%[0-9]%', REVERSE(@String)) --Flip the string and tell me from the other end where that number occurs

SELECT LEN(@String) - PATINDEX('%[0-9 ].%[0-9]%', REVERSE(@String)) - PATINDEX('%[0-9]%', @String ) +2 --substract those from the length and add 2, tells me how long it is.



--The above is basically where the number starts and it's length in the string.

--Then use substring to pull it all out.

SELECT SUBSTRING(@String, PATINDEX('%[0-9 ].%[0-9]%', @String ), LEN(@String) - PATINDEX('%[0-9]%', REVERSE(@String)) - PATINDEX('%[0-9 ].%[0-9]%', @String ) +2)



--larger sample set

IF OBJECT_ID('tempdb..#TestData') IS NOT NULL

DROP TABLE #TestData



CREATE TABLE #TestData (

NumberData NVARCHAR(100)

)



INSERT INTO #TestData (

                          [NumberData]

                      )

VALUES

('200g')

,('0.2KG')

,('0.200kg')

,('0.2')

,('0.2g(e)')

,('0.2000000k.g.')

,('grams 0.345')

,('This is k.g. 0.456 not g')

,('.456kg')



SELECT *, SUBSTRING([NumberData], PATINDEX('%[0-9 ].%[0-9]%', [NumberData] ), LEN([NumberData]) - PATINDEX('%[0-9]%', REVERSE([NumberData])) - PATINDEX('%[0-9 ].%[0-9]%', [NumberData] ) +2)FROM #TestData

You need to include . as part of a number. Rest is somewhat straight forward as long as there is a valid number present in the string after the first numeric character:

DECLARE @t TABLE (test VARCHAR(256));

INSERT INTO @t VALUES

('200g'),

('0.2kg'),

('0.200kg'),

('0.2g(e)'),

('0.2'),

('.2'),

('2KG'),

('2'),

('foo200bar'),

('foobar');



SELECT *, CAST(substr2 AS DECIMAL(10, 4)) AS result

FROM @t

CROSS APPLY (SELECT PATINDEX('%[.0-9]%', test) AS pos1) AS ca1

CROSS APPLY (SELECT CASE WHEN pos1 = 0 THEN NULL ELSE SUBSTRING(test, pos1, LEN(test) - pos1 + 1) END AS substr1) AS ca2

CROSS APPLY (SELECT PATINDEX('%[^.0-9]%', substr1) AS pos2) AS ca3

CROSS APPLY (SELECT CASE WHEN pos2 = 0 THEN substr1 ELSE SUBSTRING(substr1, 1, pos2 - 1) END AS substr2) AS ca4

Demo on DB Fiddle

Converted to function:

CREATE FUNCTION dbo.fn_GetNumeric(@test VARCHAR(256)) RETURNS DECIMAL(10, 4) AS

BEGIN

    DECLARE @pos1 AS INT = PATINDEX('%[.0-9]%', @test);

    IF @pos1 = 0 RETURN NULL;



    DECLARE @substr AS VARCHAR(256) = SUBSTRING(@test, @pos1, LEN(@test) - @pos1 + 1);



    DECLARE @pos2 AS INT = PATINDEX('%[^.0-9]%', @substr);

    IF @pos2 = 0 RETURN @substr;



    RETURN SUBSTRING(@substr, 1, @pos2 - 1);

END

Demo on DB Fiddle