SQL 서버 2008 R2에 대한 Try_Convert

나는 2008 R2 SQL Server를 사용하고 내가 CONVERT를 사용하여 DECIMAL (28,10)로 변환하고자하는 VARCHAR 열이 있어요. 이 숫자로 구문 분석 할 수 없습니다 그래서하지만 그 행의 대부분은 심하게 포맷됩니다. 이 경우 그냥 0 또는 NULL에 결과를 설정하여 사람들을 건너 뛰려.

내가 SQL 서버 2012의 새로운 문이 알고 (TRY_CONVERT는 ()) 편리하게 될 것이라고.

이것은 2008 년에 가능 또는 우리가 다음 버전의 SQL 2012로 업데이트 할 때까지 기다려야합니다?

편집하다

불행하게도 ISNUMERIC ()이 경우에는 신뢰할 수 없습니다. 나는 시도

ISNUMERIC(myCol) = 1

CONVERT 소수점으로 변환 할 수 없다는 것을 행에 대한 사실 그 돌아갑니다.

해결법

==============================
1.SQL 서버에서 XML을 사용하는 경우 당신은 데이터 형식으로 캐스팅해야 할 수 있으며, 캐스트가 실패 할 경우 널 (null) 값을받을 수 있습니다.

SQL 서버에서 XML을 사용하는 경우 당신은 데이터 형식으로 캐스팅해야 할 수 있으며, 캐스트가 실패 할 경우 널 (null) 값을받을 수 있습니다.
```
declare @T table
(
  Col varchar(50)
)

insert into @T values
('1'),
('1.1'),
('1,1'),
('1a')

select cast('' as xml).value('sql:column("Col") cast as xs:decimal ?', 
                             'decimal(28,10)') as Col
from @T
```
결과:
```
Col
-------------
1.0000000000
1.1000000000
NULL
NULL
```
==============================
2.이 영구적 인 변화를 수 있기 때문에, 그때는 2 단계 프로세스로 할 것 - 첫째, 다음 열을 변환, 잘못된 텍스트를 제거합니다.

이 영구적 인 변화를 수 있기 때문에, 그때는 2 단계 프로세스로 할 것 - 첫째, 다음 열을 변환, 잘못된 텍스트를 제거합니다.

잘못된 텍스트를 제거하려면, 내가 뭔가를 같이 할 것입니다 :
```
UPDATE [Table]
SET [Column] = NULL
WHERE [Column] LIKE '%[^0-9.]%' or
LEN([Column]) - LEN(REPLACE([Column],'.','')) > 1 or
LEN([Column]) > 28
```
그 일을 끝낼 후, 모든 유물은 열 정의를 변경하여 전환해야한다고
```
ALTER TABLE [Table] ALTER COLUMN [Column] decimal(28,10)
```
==============================
3.당신은 예를 들어 Decimal.Parse에 사용, C # 및 사용 SQLCLR에서 사용자 정의 파서를 쓸 수 있습니다 (). ISNUMERIC를 사용하는 atempt하지 마십시오, 악명 잘못된 (CAST 실패 문자열 TRUE 반환)입니다.

당신은 예를 들어 Decimal.Parse에 사용, C # 및 사용 SQLCLR에서 사용자 정의 파서를 쓸 수 있습니다 (). ISNUMERIC를 사용하는 atempt하지 마십시오, 악명 잘못된 (CAST 실패 문자열 TRUE 반환)입니다.

==============================

4.마지막으로 SO와 구글의 도움으로 만드는 방법을 알아 냈다.

마지막으로 SO와 구글의 도움으로 만드는 방법을 알아 냈다.

업데이트 문 :

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

우선은 percentvalue 마커로 사용되는 대부분의 시간 % 나 문자를 검색합니다. 뿐만 아니라 임의의 다른 용도가있다. 그것은 ISNUMERIC 내 경우에는 신뢰할 수 없습니다 것을 밝혀졌다.

정말로 차이를 만드는 것은 여기에서 isreallynumeric 저장 프로 시저를 호출합니다.

그래서

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

==============================

5.나는 SQL Server 2008의 SQL 서버 2012의 TRY_CAST 기능을 시뮬레이션 할 수있는 유용한 스칼라 함수를 썼다.

나는 SQL Server 2008의 SQL 서버 2012의 TRY_CAST 기능을 시뮬레이션 할 수있는 유용한 스칼라 함수를 썼다.

dbo.TRY_CAST(Expression, Data_Type, ReturnValueIfErrorCast)

기능 코드 :

DECLARE @strSQL NVARCHAR(1000)
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[TRY_CAST]'))
    BEGIN
        SET @strSQL = 'CREATE FUNCTION [dbo].[TRY_CAST] () RETURNS INT AS BEGIN RETURN 0 END'
        EXEC sys.sp_executesql @strSQL
    END

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

/*
------------------------------------------------------------------------------------------------------------------------
    Description:    
                    Syntax 
                    ---------------
                    dbo.TRY_CAST(Expression, Data_Type, ReturnValueIfErrorCast)

                    +---------------------------+-----------------------+
                    |   Expression              |   VARCHAR(8000)       |
                    +---------------------------+-----------------------+
                    |   Data_Type               |   VARCHAR(8000)       |
                    +---------------------------+-----------------------+
                    |   ReturnValueIfErrorCast  |   SQL_VARIANT = NULL  |
                    +---------------------------+-----------------------+


                    Arguments
                    ---------------
                    expression
                    The value to be cast. Any valid expression.

                    Data_Type
                    The data type into which to cast expression.

                    ReturnValueIfErrorCast
                    Value returned if cast fails or is not supported. Required. Set the DEFAULT value by default.


                    Return Type
                    ----------------
                    Returns value cast to SQL_VARIANT type if the cast succeeds; otherwise, returns null if the parameter @pReturnValueIfErrorCast is set to DEFAULT, 
                    or that the user indicates.


                    Remarks
                    ----------------
                    dbo.TRY_CAST function simulates the TRY_CAST function reserved of SQL SERVER 2012 for using in SQL SERVER 2008. 
                    dbo.TRY_CAST function takes the value passed to it and tries to convert it to the specified Data_Type. 
                    If the cast succeeds, dbo.TRY_CAST returns the value as SQL_VARIANT type; if the cast doesn´t succees, null is returned if the parameter @pReturnValueIfErrorCast is set to DEFAULT. 
                    If the Data_Type is unsupported will return @pReturnValueIfErrorCast.
                    dbo.TRY_CAST function requires user make an explicit CAST or CONVERT in ANY statements.
                    This version of dbo.TRY_CAST only supports CAST for INT, DATE, NUMERIC and BIT types.


                    Examples
                    ====================================================================================================

                    --A. Test TRY_CAST function returns null

                        SELECT   
                            CASE WHEN dbo.TRY_CAST('6666666166666212', 'INT', DEFAULT) IS NULL   
                            THEN 'Cast failed'  
                            ELSE 'Cast succeeded'  
                        END AS Result; 

                    GO

                    --B. Error Cast With User Value

                        SELECT   
                            dbo.TRY_CAST('2147483648', 'INT', DEFAULT) AS [Error Cast With DEFAULT],
                            dbo.TRY_CAST('2147483648', 'INT', -1) AS [Error Cast With User Value],
                            dbo.TRY_CAST('2147483648', 'INT', NULL) AS [Error Cast With User NULL Value]; 

                        GO 

                    --C. Additional CAST or CONVERT required in any assignment statement

                        DECLARE @IntegerVariable AS INT

                        SET @IntegerVariable = CAST(dbo.TRY_CAST(123, 'INT', DEFAULT) AS INT)

                        SELECT @IntegerVariable

                        GO 

                        IF OBJECT_ID('tempdb..#temp') IS NOT NULL
                            DROP TABLE #temp

                        CREATE TABLE #temp (
                            Id INT IDENTITY
                            , FieldNumeric NUMERIC(3, 1)
                            )

                        INSERT INTO dbo.#temp (FieldNumeric)
                        SELECT CAST(dbo.TRY_CAST(12.3, 'NUMERIC(3,1)', 0) AS NUMERIC(3, 1));--Need explicit CAST on INSERT statements

                        SELECT *
                        FROM #temp

                        DROP TABLE #temp

                        GO 

                    --D. Supports CAST for INT, DATE, NUMERIC and BIT types.

                        SELECT dbo.TRY_CAST(2147483648, 'INT', 0) AS [Cast failed]
                            , dbo.TRY_CAST(2147483647, 'INT', 0) AS [Cast succeeded]
                            , SQL_VARIANT_PROPERTY(dbo.TRY_CAST(212, 'INT', 0), 'BaseType') AS [BaseType];

                        SELECT dbo.TRY_CAST('AAAA0101', 'DATE', DEFAULT) AS [Cast failed]
                            , dbo.TRY_CAST('20160101', 'DATE', DEFAULT) AS [Cast succeeded]
                            , SQL_VARIANT_PROPERTY(dbo.TRY_CAST('2016-01-01', 'DATE', DEFAULT), 'BaseType') AS [BaseType];

                        SELECT dbo.TRY_CAST(1.23, 'NUMERIC(3,1)', DEFAULT) AS [Cast failed]
                            , dbo.TRY_CAST(12.3, 'NUMERIC(3,1)', DEFAULT) AS [Cast succeeded]
                            , SQL_VARIANT_PROPERTY(dbo.TRY_CAST(12.3, 'NUMERIC(3,1)', DEFAULT), 'BaseType') AS [BaseType];

                        SELECT dbo.TRY_CAST('A', 'BIT', DEFAULT) AS [Cast failed]
                            , dbo.TRY_CAST(1, 'BIT', DEFAULT) AS [Cast succeeded]
                            , SQL_VARIANT_PROPERTY(dbo.TRY_CAST('123', 'BIT', DEFAULT), 'BaseType') AS [BaseType];

                        GO 

                    --E. B. TRY_CAST return NULL on unsupported data_types

                        SELECT dbo.TRY_CAST(4, 'xml', DEFAULT) AS [unsupported];  

                        GO  

                    ====================================================================================================

------------------------------------------------------------------------------------------------------------------------
    Responsible:    Javier Pardo 
    Date:           diciembre 29/2016
    WB tests:       Javier Pardo 
------------------------------------------------------------------------------------------------------------------------
    Update by:      Javier Eduardo Pardo Moreno 
    Date:           febrero 16/2017
    Id update:      JEPM20170216
    Description:    Fix  ISNUMERIC function makes it unreliable. SELECT dbo.TRY_CAST('+', 'INT', 0) will yield Msg 8114, 
                    Level 16, State 5, Line 16 Error converting data type varchar to float.
                    ISNUMERIC() function treats few more characters as numeric, like: – (minus), + (plus), $ (dollar), \ (back slash), (.)dot and (,)comma
                    Collaborator aperiooculus (http://stackoverflow.com/users/3083382/aperiooculus )

                    Fix dbo.TRY_CAST('2013/09/20', 'datetime', DEFAULT) for supporting DATETIME format

    WB tests:       Javier Pardo

------------------------------------------------------------------------------------------------------------------------
*/

ALTER FUNCTION dbo.TRY_CAST
(
    @pExpression AS VARCHAR(8000),
    @pData_Type AS VARCHAR(8000),
    @pReturnValueIfErrorCast AS SQL_VARIANT = NULL
)
RETURNS SQL_VARIANT
AS
BEGIN
    --------------------------------------------------------------------------------
    --  INT 
    --------------------------------------------------------------------------------

    IF @pData_Type = 'INT'
    BEGIN
        IF ISNUMERIC(@pExpression) = 1 AND @pExpression NOT IN ('-','+','$','.',',','\')    --JEPM20170216
        BEGIN
            DECLARE @pExpressionINT AS FLOAT = CAST(@pExpression AS FLOAT)

            IF @pExpressionINT BETWEEN - 2147483648.0 AND 2147483647.0
            BEGIN
                RETURN CAST(@pExpressionINT as INT)
            END
            ELSE
            BEGIN
                RETURN @pReturnValueIfErrorCast
            END --FIN IF @pExpressionINT BETWEEN - 2147483648.0 AND 2147483647.0
        END
        ELSE
        BEGIN
            RETURN @pReturnValueIfErrorCast
        END -- FIN IF ISNUMERIC(@pExpression) = 1
    END -- FIN IF @pData_Type = 'INT'

    --------------------------------------------------------------------------------
    --  DATE    
    --------------------------------------------------------------------------------

    IF @pData_Type IN ('DATE','DATETIME')
    BEGIN
        IF ISDATE(@pExpression) = 1
        BEGIN

            DECLARE @pExpressionDATE AS DATETIME = cast(@pExpression AS DATETIME)

            IF @pData_Type = 'DATE'
            BEGIN
                RETURN cast(@pExpressionDATE as DATE)
            END

            IF @pData_Type = 'DATETIME'
            BEGIN
                RETURN cast(@pExpressionDATE as DATETIME)
            END

        END
        ELSE 
        BEGIN

            DECLARE @pExpressionDATEReplaced AS VARCHAR(50) = REPLACE(REPLACE(REPLACE(@pExpression,'\',''),'/',''),'-','')

            IF ISDATE(@pExpressionDATEReplaced) = 1
            BEGIN
                IF @pData_Type = 'DATE'
                BEGIN
                    RETURN cast(@pExpressionDATEReplaced as DATE)
                END

                IF @pData_Type = 'DATETIME'
                BEGIN
                    RETURN cast(@pExpressionDATEReplaced as DATETIME)
                END

            END
            ELSE
            BEGIN
                RETURN @pReturnValueIfErrorCast
            END
        END --FIN IF ISDATE(@pExpression) = 1
    END --FIN IF @pData_Type = 'DATE'

    --------------------------------------------------------------------------------
    --  NUMERIC 
    --------------------------------------------------------------------------------

    IF @pData_Type LIKE 'NUMERIC%'
    BEGIN

        IF ISNUMERIC(@pExpression) = 1
        BEGIN

            DECLARE @TotalDigitsOfType AS INT = SUBSTRING(@pData_Type,CHARINDEX('(',@pData_Type)+1,  CHARINDEX(',',@pData_Type) - CHARINDEX('(',@pData_Type) - 1)
                , @TotalDecimalsOfType AS INT = SUBSTRING(@pData_Type,CHARINDEX(',',@pData_Type)+1,  CHARINDEX(')',@pData_Type) - CHARINDEX(',',@pData_Type) - 1)
                , @TotalDigitsOfValue AS INT 
                , @TotalDecimalsOfValue AS INT 
                , @TotalWholeDigitsOfType AS INT 
                , @TotalWholeDigitsOfValue AS INT 

            SET @pExpression = REPLACE(@pExpression, ',','.')

            SET @TotalDigitsOfValue = LEN(REPLACE(@pExpression, '.',''))
            SET @TotalDecimalsOfValue = CASE Charindex('.', @pExpression)
                                        WHEN 0
                                            THEN 0
                                        ELSE Len(Cast(Cast(Reverse(CONVERT(VARCHAR(50), @pExpression, 128)) AS FLOAT) AS BIGINT))
                                        END 
            SET @TotalWholeDigitsOfType = @TotalDigitsOfType - @TotalDecimalsOfType
            SET @TotalWholeDigitsOfValue = @TotalDigitsOfValue - @TotalDecimalsOfValue

            -- The total digits can not be greater than the p part of NUMERIC (p, s)
            -- The total of decimals can not be greater than the part s of NUMERIC (p, s)
            -- The total digits of the whole part can not be greater than the subtraction between p and s
            IF (@TotalDigitsOfValue <= @TotalDigitsOfType) AND (@TotalDecimalsOfValue <= @TotalDecimalsOfType) AND (@TotalWholeDigitsOfValue <= @TotalWholeDigitsOfType)
            BEGIN
                DECLARE @pExpressionNUMERIC AS FLOAT
                SET @pExpressionNUMERIC = CAST (ROUND(@pExpression, @TotalDecimalsOfValue) AS FLOAT) 

                RETURN @pExpressionNUMERIC --Returns type FLOAT
            END 
            else
            BEGIN
                RETURN @pReturnValueIfErrorCast
            END-- FIN IF (@TotalDigitisOfValue <= @TotalDigits) AND (@TotalDecimalsOfValue <= @TotalDecimals) 

        END
        ELSE 
        BEGIN
            RETURN @pReturnValueIfErrorCast
        END --FIN IF ISNUMERIC(@pExpression) = 1
    END --IF @pData_Type LIKE 'NUMERIC%'

    --------------------------------------------------------------------------------
    --  BIT 
    --------------------------------------------------------------------------------

    IF @pData_Type LIKE 'BIT'
    BEGIN
        IF ISNUMERIC(@pExpression) = 1
        BEGIN
            RETURN CAST(@pExpression AS BIT) 
        END
        ELSE 
        BEGIN
            RETURN @pReturnValueIfErrorCast
        END --FIN IF ISNUMERIC(@pExpression) = 1
    END --IF @pData_Type LIKE 'BIT'


    --------------------------------------------------------------------------------
    --  FLOAT   
    --------------------------------------------------------------------------------

    IF @pData_Type LIKE 'FLOAT'
    BEGIN
        IF ISNUMERIC(REPLACE(REPLACE(@pExpression, CHAR(13), ''), CHAR(10), '')) = 1
        BEGIN

            RETURN CAST(@pExpression AS FLOAT) 
        END
        ELSE 
        BEGIN

            IF REPLACE(@pExpression, CHAR(13), '') = '' --Only white spaces are replaced, not new lines
            BEGIN
                RETURN 0
            END
            ELSE 
            BEGIN
                RETURN @pReturnValueIfErrorCast
            END --IF REPLACE(@pExpression, CHAR(13), '') = '' 

        END --FIN IF ISNUMERIC(@pExpression) = 1
    END --IF @pData_Type LIKE 'FLOAT'

    --------------------------------------------------------------------------------
    --  Any other unsupported data type will return NULL or the value assigned by the user to @pReturnValueIfErrorCast  
    --------------------------------------------------------------------------------

    RETURN @pReturnValueIfErrorCast



END

지금은 단지 데이터 유형 INT, DATE, DATETIME, 숫자, BIT 및 FLOAT를 지원합니다. 당신은 아래의 다음 링크에서이 코드의 마지막 버전을 찾을 수 있고 우리는 그것을 개선하기 위해 서로 도움이됩니다. SQL Server에 대한 TRY_CAST 기능 2008 https://gist.github.com/jotapardo/800881eba8c5072eb8d99ce6eb74c8bb

==============================

6.나는 간단한 솔루션을 선호하고 다음과 같은 SP를했다.

나는 간단한 솔루션을 선호하고 다음과 같은 SP를했다.

CREATE PROC TRY_CAST 
(   
    @valueToBeParsed VARCHAR(64), @parsedValue INT OUTPUT
)
AS
BEGIN
    BEGIN TRY
        SELECT @parsedValue = cast(@valueToBeParsed as int)
    END TRY
    BEGIN CATCH
    SET @parsedValue = null
    END CATCH
END
GO

그리고 다음과 같이 그것을 사용

DECLARE @val int
EXEC TRY_CAST '1w', @val out
select @val

==============================
7.
```
CASE WHEN ISNUMERIC(yourColumn) = 1 
THEN CAST(yourColumn AS DECIMAL(28,10)) 
ELSE NULL --or Zero
END AS yourColumName
```
내가 같은 질문을했다, 그래서 2019과는 여전히 2008 서버를 사용하여. 위는 나를 위해 일했다.
==============================
8.SQL 서버 2008 R2는 기능 TRY_CAST을 가지고 있지만 2005 호환성으로 DB를 config (설정)하는 경우는 SQL 서버를-인식하지 발견했다.

SQL 서버 2008 R2는 기능 TRY_CAST을 가지고 있지만 2005 호환성으로 DB를 config (설정)하는 경우는 SQL 서버를-인식하지 발견했다.

나는 @jotapardo을 만들 수 있지만이 경우 인식 기능에 TRY_CAST2 때문에 SS의 이름을 변경 기능을 사용했다.

from https://stackoverflow.com/questions/17941035/try-convert-for-sql-server-2008-r2 by cc-by-sa and MIT license

'SQL' 카테고리의 다른 글

[SQL] 더 : FOR 커서 루프 또는 간단한 선택 오라클 데이터베이스에 대량 삽입? (0)	2020.06.25
[SQL] TSQL - 그것은 정렬 순서를 정의 할 수 있습니까? (0)	2020.06.25
[SQL] OLE DB 공급자 연결된 서버 "Microsoft.ACE.OLEDB.12.0" "(널)" (0)	2020.06.25
[SQL] 그것은 SQL 쿼리의 텍스트 파일을 실행할 수 있습니까? (0)	2020.06.25
[SQL] 에 "&"가 포함 된 문자열을 삽입하는 방법 (0)	2020.06.25

복붙노트

[SQL] SQL 서버 2008 R2에 대한 Try_Convert

SQL 서버 2008 R2에 대한 Try_Convert

해결법

1.SQL 서버에서 XML을 사용하는 경우 당신은 데이터 형식으로 캐스팅해야 할 수 있으며, 캐스트가 실패 할 경우 널 (null) 값을받을 수 있습니다.

2.이 영구적 인 변화를 수 있기 때문에, 그때는 2 단계 프로세스로 할 것 - 첫째, 다음 열을 변환, 잘못된 텍스트를 제거합니다.

3.당신은 예를 들어 Decimal.Parse에 사용, C # 및 사용 SQLCLR에서 사용자 정의 파서를 쓸 수 있습니다 (). ISNUMERIC를 사용하는 atempt하지 마십시오, 악명 잘못된 (CAST 실패 문자열 TRUE 반환)입니다.

4.마지막으로 SO와 구글의 도움으로 만드는 방법을 알아 냈다.

5.나는 SQL Server 2008의 SQL 서버 2012의 TRY_CAST 기능을 시뮬레이션 할 수있는 유용한 스칼라 함수를 썼다.

6.나는 간단한 솔루션을 선호하고 다음과 같은 SP를했다.

7.

8.SQL 서버 2008 R2는 기능 TRY_CAST을 가지고 있지만 2005 호환성으로 DB를 config (설정)하는 경우는 SQL 서버를-인식하지 발견했다.

'SQL' 카테고리의 다른 글

티스토리툴바