Try_Convert for SQL Server 2008 R2

Try_Convert for SQL Server 2008 R2

Finally found out how to make it with the help from SO and Google.

The update statement:

UPDATE PriceTerm 
SET PercentAddition = CONVERT(decimal(28,10), RTRIM(LTRIM(REPLACE(REPLACE(REPLACE(AdditionalDescription,'%',''), ',','.'), '&', '')))) 
WHERE AdditionalDescription LIKE '%[%]%' AND
dbo.isreallynumeric(RTRIM(LTRIM(REPLACE(REPLACE(REPLACE(AdditionalDescription,'%',''), ',','.'), '&', '')))) = 1 AND
PercentAddition = 0

First I search for % char as most of the times that is used as a marker for the percentvalue. But there is also random other uses. It turned out that ISNUMERIC was not reliable in my case.

What really make difference is the call to stored procedure isreallynumeric from here.

So

CREATE FUNCTION dbo.isReallyNumeric  
(  
    @num VARCHAR(64)  
)  
RETURNS BIT  
BEGIN  
    IF LEFT(@num, 1) = '-'  
        SET @num = SUBSTRING(@num, 2, LEN(@num))  

    DECLARE @pos TINYINT  

    SET @pos = 1 + LEN(@num) - CHARINDEX('.', REVERSE(@num))  

    RETURN CASE  
    WHEN PATINDEX('%[^0-9.-]%', @num) = 0  
        AND @num NOT IN ('.', '-', '+', '^') 
        AND LEN(@num)>0  
        AND @num NOT LIKE '%-%' 
        AND  
        (  
            ((@pos = LEN(@num)+1)  
            OR @pos = CHARINDEX('.', @num))  
        )  
    THEN  
        1  
    ELSE  
    0  
    END  
END  
GO 

How can I replace not supported TRY_CONVERT function on an old SQL Server 2008 version?

Filling somes blanks here with my own guesses, but basing on what the OP has, I suspect we could do something like this instead:

SELECT *
FROM [dbo].[PROT_TITOLARIO] PT
     CROSS APPLY (VALUES(CASE WHEN PT.IdSottocategoria LIKE '%[^0-9.]%' THEN NULL ELSE PT.IdSottocategoria END)) TC(IdSottocategoria)
     CROSS APPLY (VALUES(CONVERT(hierarchyid,'/'+REPLACE(TC.IdSottocategoria,'.','/')+'/')))V(Hid)
ORDER BY CASE WHEN V.Hid IS NULL THEN 1 ELSE 0 END,
         V.Hid,
         PT.IdSottocategoria;

I use a CASE expression to check that the value of IdSottocategoria only contains numerical values and .'s and then just CONVERT those values. That works for the sample values the OP supplied: db<>fiddle

To reiterate my comments, SQL Server 2008 R2 SP2 is a really old version of SQL Server. SP3 came out some time ago for 2008R2, and SP2 is not patched against the Spectre and Meltdown vulnerabilities. Even if you aren't updating the version of SQL Server for a bit (which should heavily be reconsidered) I cannot more strongly suggest you get that server updated to SP3 GDR. This is especially true if you are in a country that has GDPR (or similar) legislation, as your local authority will see unpatched (and unsupported) software as a huge concern and will not reflect well on the list of preventative measures in the result of a breach.

try_convert in SQL Server 2008 R2 Express

try_convert() attempts to do the convert. This is a nice way of handling conversion errors (or at least better than SQL Server previously did, which was nothing). You can use like to see if the string "looks" like a valid decimal. Here is one attempt:

CASE WHEN tew_userdata_locTo.use_data0 not like '%[^0-9.]%' and
          tew_userdata_locTo.use_data0 not like '%.%.%'
     THEN convert(decimal, tew_userdata_locTo.use_data0)
THEN .. ELSE .. END

Querying String Columns as Numeric

For all intents and purposes, I was able to take the original query into a Table-Valued function. Then I could apply the where clause as expected to the result of the UDF.

While I'm marking this as the accepted answer, I'm still interested in knowing if there's a technique that can be applied at the query level to get this to work.

How to check for the correct datetime format in TSQL function?

I assume the best approach is create another tsql function where I could check for dot positions in strings like

22.03.2016
28.02.2017 00:00:00

So I will use combination of following statements

SELECT CHARINDEX('.', '13.05.2019') AS FirstDot; 
3 -- ОК

SELECT CHARINDEX('.', '13.05.2019', 4) AS SecondDot;
6 -- ОК

SELECT CHARINDEX('.', '13.005.2019', 4) AS MatchPosition; 
7 --- NOT OK

---

Related Topics