none
How to return varchar field that contains alphanumeric chars and decimal place.

    질문

  • Hi I need to query a table where one of the fields to be returned contains data like 'L03.11'

    Fields datatype is varchar(9)

    But when I run the query I get this msg "Error converting data type varchar to numeric."

    Obviously sql server sees that field as numeric because of the decimal point but some of the other chars are not numeric

    hence the error msg.

    All I need is a string doesn't have to be numeric.

    TIA

    2018년 6월 13일 수요일 오전 3:55

답변

  • Thats not the correct analysis I'm afraid

    SQL doesnt see it as Numeric by itself as it knows datatype is varchar

    Only case where it can happen is when you're doing some operations using this column like say join condition, concatenation etc where one of the involved columns is of numeric. Since as per datatype precedence Numeric comes before varchar based datatypes, SQLserver tries to implicitly convert the varchar column to Numeric which will break when it has non numeric values like this

    https://docs.microsoft.com/en-us/sql/t-sql/data-types/data-type-precedence-transact-sql?view=sql-server-2017

    So in that case the solution has to be to explicitly convert the numeric columns to varchar before concatenation using = or in the ON condition of the join. Another option if performing concatenation is to use the function CONCAT available from 2012 version which automatically does this conversion under the hood

    So to illustrate see

    declare @n Numeric(10,2) = 13.62
    
    declare @s varchar(20)= 'L3.6'
    
    --this will error out due to implicit conversion
    SELECT @n + ' ' + @s
    
    --this will not error due to xplicitly converting values to varchar
    SELECT CAST(@n as varchar(10)) + ' ' + @s
    
    --this will not error due to xplicitly converting values to varchar
    SELECT CONCAT(@n, ' ', @s)


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page


    • 편집됨 Visakh16MVP 2018년 6월 13일 수요일 오전 6:16
    • 답변으로 표시됨 ghw123 2018년 6월 14일 목요일 오후 7:26
    2018년 6월 13일 수요일 오전 6:16

모든 응답

  • Hi TIA,

    Per your post, what is your exact query statement? And what is desired output for the field? Please provide more detailed description about your requirement.

    Best Regards,

    Will


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    2018년 6월 13일 수요일 오전 4:52
  • If the non-numeric part always comes at the beginning, you can try something like this..

    declare @t table(a varchar(9))
    
    insert into @t values('L03.11')
    
    select cast(a as numeric(5,2)) from @t -- Error
    
    select stuff(a, 1, patindex('%[0-9]%', a)-1, '') from @t -- No error
    2018년 6월 13일 수요일 오전 5:03
  • Thats not the correct analysis I'm afraid

    SQL doesnt see it as Numeric by itself as it knows datatype is varchar

    Only case where it can happen is when you're doing some operations using this column like say join condition, concatenation etc where one of the involved columns is of numeric. Since as per datatype precedence Numeric comes before varchar based datatypes, SQLserver tries to implicitly convert the varchar column to Numeric which will break when it has non numeric values like this

    https://docs.microsoft.com/en-us/sql/t-sql/data-types/data-type-precedence-transact-sql?view=sql-server-2017

    So in that case the solution has to be to explicitly convert the numeric columns to varchar before concatenation using = or in the ON condition of the join. Another option if performing concatenation is to use the function CONCAT available from 2012 version which automatically does this conversion under the hood

    So to illustrate see

    declare @n Numeric(10,2) = 13.62
    
    declare @s varchar(20)= 'L3.6'
    
    --this will error out due to implicit conversion
    SELECT @n + ' ' + @s
    
    --this will not error due to xplicitly converting values to varchar
    SELECT CAST(@n as varchar(10)) + ' ' + @s
    
    --this will not error due to xplicitly converting values to varchar
    SELECT CONCAT(@n, ' ', @s)


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page


    • 편집됨 Visakh16MVP 2018년 6월 13일 수요일 오전 6:16
    • 답변으로 표시됨 ghw123 2018년 6월 14일 목요일 오후 7:26
    2018년 6월 13일 수요일 오전 6:16
  • But when I run the query I get this msg

    Hello,

    When you query only that one table without any join on an other table, then SQL Server returns the data without any implicite conversion.

    My guess ito the blue: There is a compute column which returns the value as numeric data type and that conversion Fails. Please post table design as DDL Statement.


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    2018년 6월 13일 수요일 오전 7:17
  • Thank you for your replies.

    This worked.

    SELECT  T5.CODED,T5.DPTEDCNT,T5.PTEDTYPE,T2.AURNO 
    FROM (SELECT T5.DPTEDADM,CAST(T5.PTEDCODE AS VARCHAR(3))+' - '+CAST(T5.PTEDDESC AS VARCHAR(30)) AS CODED,T5.DPTEDCNT,T5.PTEDTYPE FROM PAS.PATECDAF T5  WHERE T5.PTEDTYPE='A')T5 
    				INNER JOIN PAS.PATMI1AF T2 ON T2.DAADMNO=T5.DPTEDADM

    2018년 6월 13일 수요일 오후 8:11
  • Thank you for your replies.

    This worked.

    SELECT  T5.CODED,T5.DPTEDCNT,T5.PTEDTYPE,T2.AURNO 
    FROM (SELECT T5.DPTEDADM,CAST(T5.PTEDCODE AS VARCHAR(3))+' - '+CAST(T5.PTEDDESC AS VARCHAR(30)) AS CODED,T5.DPTEDCNT,T5.PTEDTYPE FROM PAS.PATECDAF T5  WHERE T5.PTEDTYPE='A')T5 
    				INNER JOIN PAS.PATMI1AF T2 ON T2.DAADMNO=T5.DPTEDADM

    Since you have got your answer, please kindly close the thread by marking the useful reply as answer.

    Thanks for your contribution.

    Best Regards,

    Will


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    2018년 6월 14일 목요일 오전 9:31