Table Join Dissimilar data RRS feed

  • คำถาม

  • I have this query

    SELECT Sum(APL.TotalVal)
    FROM APTransactionline APL
    JOIN APTransaction APH ON APL.TransactionNumber = APH.TransactionNumber
    WHERE APL.Jobnumber = T.DocumentNumber
    AND APL.GLCode = '10260.00'

    APL.Jobnumber is always a string of numbers VarChar(20)

    T.DocumentNumber is also VarChar(20) but it can contain numbers and characters

    Example: 23659STG   or  23659R  or  23659-2

    I need a way to join the tables only on the number part of the string.

    I tried this: select CAST(DocumentNumber as int) as jnum from ArTransaction

    and it returned

    Msg 245, Level 16, State 1, Line 1
    Conversion failed when converting the varchar value '2679PSTG' to data type int.

    19 พฤศจิกายน 2562 21:57


  • Try to use TRY_CAST instead of CAST. It returns NULL if it is not a number. If Jobnumber is nullable, add APL.Jobnumber is NOT NULL in the WHERE clause.

    A Fan of SSIS, SSRS and SSAS

    • แก้ไขโดย Guoxiong Yuan 19 พฤศจิกายน 2562 22:03
    19 พฤศจิกายน 2562 22:02
  • This expression:

    substring(col, 1, patindex('%[^0-9]%', col + 'XX') - 1)

    gives you the numeric part only. It's making the assumption that that number comes first in the string.

    You need to apply this expression on both columns.

    This operation will render any index on the columns useless.

    Erland Sommarskog, SQL Server MVP,

    • เสนอเป็นคำตอบโดย Rachel_WangMicrosoft 25 พฤศจิกายน 2562 7:57
    19 พฤศจิกายน 2562 23:01
  • Hi Ruthless Roth, 

    Or please create a function to remove other stings which are not number . 

    Create Function [dbo].[RemoveNoNumCharacters]
    (@Temp VarChar(1000))Returns VarChar(1000)AS
        Declare @KeepValues as varchar(50)
        Set @KeepValues = '%[^0-9]%'
        While PatIndex(@KeepValues, @Temp) > 0
            Set @Temp = Stuff(@Temp, PatIndex(@KeepValues, @Temp), 1, '')
        Return @Temp
    Select dbo.RemoveNonAlphaCharacters('abc1234def5678ghi90jkl')
    select [dbo].RemoveNonAlphaCharacters(DocumentNumber) jnum from ArTransaction

    Best Regards,


    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

    • เสนอเป็นคำตอบโดย Rachel_WangMicrosoft 25 พฤศจิกายน 2562 7:57
    20 พฤศจิกายน 2562 2:55
  • Try this too:

    . . .

    WHERE ( T.DocumentNumber = APL.Jobnumber

       OR T.DocumentNumber LIKE APL.Jobnumber + '[^0-9]%' )

    AND . . .

    • เสนอเป็นคำตอบโดย Rachel_WangMicrosoft 25 พฤศจิกายน 2562 7:57
    20 พฤศจิกายน 2562 6:24