Joining tables with different field types 1

[jchewsmith]

I am trying to join tables where one has a field type of int and the other has a field type of varchar(6) and the varchar(6) also need to be left trimmed.


Sample:

Varchar(6) integer

123456-001 = 123456

so I want to trim the 123456-001 to 123456 and change it to an integer or trim this then change the integer to a varchar.

 

[bborissov]

DECLARE @Test varchar(10) = '123456-001'
SELECT CASE WHEN ISNUMERIC(@Test) = 1
                  THEN CAST(@Test  as int)
            WHEN CHARINDEX('-', @Test) > 0 AND ISNUMERIC(LEFT(@Test,CHARINDEX('-', @Test)-1)) = 1
                 THEN CAST(LEFT(@Test,CHARINDEX('-', @Test)-1)  as int)
        END

Borislav Borissov
VFP9 SP2, SQL Server