none
converting int. to varchar

    Question

  • I wrote  a query that  takes the field from one record and compares it to a different field in a different record.  When I did it in access it worked.  But I did a copie and paste into management studio and it is not working.

    SELECT     A.TRAN_ID, B.REFERENCE_DATA
    FROM         dbo.BSTRN_HEADER AS B LEFT OUTER JOIN
                 dbo.BSTRN_HEADER AS A ON B.REFERENCE_DATA LIKE A.TRAN_ID  &'RECL'

    I get the following error message:

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

    So then I try:

    SELECT     A.TRAN_ID, B.REFERENCE_DATA
    FROM         dbo.BSTRN_HEADER AS B LEFT OUTER JOIN
                 dbo.BSTRN_HEADER AS A ON B.REFERENCE_DATA LIKE cast(A.TRAN_ID as varchar)  & 'RECL'

    I get the following error
    Msg 402, Level 16, State 1, Line 1
    The data types varchar and varchar are incompatible in the boolean AND operator.

    Tran_id is an integer and reference data is a varchar and I need to convert tran_id to a Varchar





    Monday, August 20, 2007 5:36 PM

Answers

  • SQL uses a plus sign [ + ] to concatenate -NOT the ampersand [ & ] sign used in VB/VBA/Access. In some circumstances, the ampersand is used in SQL as a boolean operator, in other situations, it is used as a string 'wildcard'.

     

     

     

    Monday, August 20, 2007 5:55 PM
    Moderator
  • The following may seem like a bit of effort, but it will be worth the effort in terms of performance. (Since your sample data indicated a leading zero for Tran_ID, I suspect that it is a varchar(). If so, remove the CAST() in the first ADD COLUMN.)

     

    If your desire is to retreive ONLY the rows where the first characters of Reference_Data match the Tran_ID AND are followed by the characters 'RECL', take the following steps:

    • Add two computed columns to the table.

    ALTER TABLE dbo.Bstrn_Header

       ADD TranRef AS ( cast( Tran_ID as varchar(10)) + 'Recl' )

     

    ALTER TABLE dbo.Bstrn_Header

       ADD RefTran AS ( left( Reference_Data, (patindex( '%Recl%', Reference_Data ) + 3 )))

    • Create indexes.

    CREATE INDEX ix_BstrnHeader_TranRef

       ON dbo.Bstm_Header( TranRef )

     

    CREATE INDEX ix_BstrnHeader_RefTran

       ON dbo.Bstm_Header( RefTran )

     

    • Find the Rows you seek.

    SELECT

       b1.Tran_ID,

       b2.Reference_Data

    FROM dbo.BSTRN_HEADER b1

       JOIN dbo.BSTRN_HEADER b2

          ON b2.TranRef = b1.RefTran

     

    When you are finished, you can remove the computed columns and indexes.

     

    DROP INDEX dbo.Bstrn_Header.ix_BstrnHeader_TranRef

    DROP INDEX dbo.Bstrn_Header.ix_BstrnHeader_RefTran

     

    ALTER TABLE dbo.Bstrn_Header

       DROP COLUMN TranRef

     

    ALTER TABLE dbo.Bstrn_Header

       DROP COLUMN RefTran

     

    This 'should' be far faster than any other method, including the time to build the indexes.

    Tuesday, August 21, 2007 11:35 PM
    Moderator

All replies

  • SQL uses a plus sign [ + ] to concatenate -NOT the ampersand [ & ] sign used in VB/VBA/Access. In some circumstances, the ampersand is used in SQL as a boolean operator, in other situations, it is used as a string 'wildcard'.

     

     

     

    Monday, August 20, 2007 5:55 PM
    Moderator
  • In SQL Server & means bit wise AND and it only accept integer operands, if you are trying to concatenate the string with the id use the following query…

     

    SELECT     A.TRAN_ID, B.REFERENCE_DATA

    FROM         dbo.BSTRN_HEADER AS B LEFT OUTER JOIN

                 dbo.BSTRN_HEADER AS A ON B.REFERENCE_DATA LIKE cast(A.TRAN_ID as varchar) + 'RECL'

     

    Otherwise let us know in detail.

     

    Monday, August 20, 2007 5:56 PM
  • In SQL Server, the ampersand (&) is a Boolean AND operator, rather than a string concatenation operator. To join two strings/varchars together, simply use the plus (+) operator.

    Code Snippet

    A.TRAN_ID + 'RECL'




    Monday, August 20, 2007 5:57 PM
  • ok No wonder, I am just learning how to write queries in sql, a little different then writing them in access.  I replaced the ampersand with a + but on my  tran id column I am getting null, and on my reference_data column, it is giving me everything but I only want the  ones that contain "RECL".
    Tuesday, August 21, 2007 1:45 PM
  • Hi, try this query, hope it'll work.

     

    SELECT

            A.TRAN_ID

           ,B.REFERENCE_DATA

    FROM dbo.BSTRN_HEADER AS B

    JOIN dbo.BSTRN_HEADER AS A

    ON B.REFERENCE_DATA LIKE str(isnull(A.TRAN_ID,'')) + 'RECL'

     

    Please let me know If I'm wrong...

    Tuesday, August 21, 2007 2:52 PM
  • Sorry to say it did not work, it was executing for about an hour and 45 min, ( I had totally forgotten about it), then I started to get phone calls saying our system was running really slow.  So I had to cancel it but it had not returned anything yet.

    These are the results I am trying to get: 

    Tran_Id          Reference_Data

    041344          041344Recl4
    672167          672167Recl1
    462341          42466677898
    123890          7892Recl1

    I only want the first 2 records-  The tran_Id is not the actuall transaction Id for that reference data, its just that the 1st 6 numbers matched.  The Reference Data with the Recl are records that were Reclassed and when the tran Id matches the reference data, that tran_id belongs to the original record that was reclassed. 
    Tuesday, August 21, 2007 8:24 PM
  • The following may seem like a bit of effort, but it will be worth the effort in terms of performance. (Since your sample data indicated a leading zero for Tran_ID, I suspect that it is a varchar(). If so, remove the CAST() in the first ADD COLUMN.)

     

    If your desire is to retreive ONLY the rows where the first characters of Reference_Data match the Tran_ID AND are followed by the characters 'RECL', take the following steps:

    • Add two computed columns to the table.

    ALTER TABLE dbo.Bstrn_Header

       ADD TranRef AS ( cast( Tran_ID as varchar(10)) + 'Recl' )

     

    ALTER TABLE dbo.Bstrn_Header

       ADD RefTran AS ( left( Reference_Data, (patindex( '%Recl%', Reference_Data ) + 3 )))

    • Create indexes.

    CREATE INDEX ix_BstrnHeader_TranRef

       ON dbo.Bstm_Header( TranRef )

     

    CREATE INDEX ix_BstrnHeader_RefTran

       ON dbo.Bstm_Header( RefTran )

     

    • Find the Rows you seek.

    SELECT

       b1.Tran_ID,

       b2.Reference_Data

    FROM dbo.BSTRN_HEADER b1

       JOIN dbo.BSTRN_HEADER b2

          ON b2.TranRef = b1.RefTran

     

    When you are finished, you can remove the computed columns and indexes.

     

    DROP INDEX dbo.Bstrn_Header.ix_BstrnHeader_TranRef

    DROP INDEX dbo.Bstrn_Header.ix_BstrnHeader_RefTran

     

    ALTER TABLE dbo.Bstrn_Header

       DROP COLUMN TranRef

     

    ALTER TABLE dbo.Bstrn_Header

       DROP COLUMN RefTran

     

    This 'should' be far faster than any other method, including the time to build the indexes.

    Tuesday, August 21, 2007 11:35 PM
    Moderator
  •  

    you're recommending meta data change. this will lock the table each time you change meta data. it's worth considering indexed view instead.

     

     

    Wednesday, August 22, 2007 3:59 AM
  • If this is an 'on-going' situation, I agree with you, an indexed VIEW would be preferred.

     

    But I read the need as a 'one-shot' operation with significant performance issues.

     

    Wednesday, August 22, 2007 4:36 AM
    Moderator
  • Hi,

     

       Please try this query,

     

    SELECT

    A.TRAN_ID

    ,B.REFERENCE_DATA

    FROM dbo.BSTRN_HEADER AS B

    JOIN dbo.BSTRN_HEADER AS A

    ON B.REFERENCE_DATA LIKE '%'+convert(varchar(20),A.ID)+'RECL%'

     

    If you dont want join operation just try it out with the below query and check the same

     

    select ID,REFERENCE_DATA from BSTRN_HEADER where REFERENCE_DATA like

    '%'+convert(varchar(20),ID)+'RECL%'

     

    Wednesday, August 22, 2007 8:39 AM