SQL searches for multiple fields, returns one

Question

I have a query that needs to compare a value with multiple columns and only return results from one. I keep getting a conversion error when it tries to convert a string argument to an integer, but I could not find a good way around this.

SELECT DISTINCT
  CASE
    WHEN table_one.integer_col = CAST('argument%' AS int)
        THEN table_one.integer_col
    WHEN table_one.varchar_col LIKE 'argument%'
        THEN table_one.varchar_col
    WHEN table_two.varchar_col LIKE 'argument%'
  END
  FROM table_one
  INNER JOIN table_two
  ON table_one.pk=table_two.fk
  WHERE
    table_one.integer_col = CAST('argument%' AS int)
    table_one.varchar_col LIKE 'argument%' OR
    table_two.varchar_col LIKE 'argument%'

This works when the "argument%" can be successfully converted to an integer, but when this is not possible, the request is bombed.

Answer 1

How to convert or distinguish an integer to a varchar data type? Instead of converting your varchar lookup argument to int to match an int column, convert the int column to varchar.

Here is a simple example.

DECLARE @Table  TABLE
(
someInt     int
)

INSERT INTO @Table (SomeInt)
    SELECT  1234567 UNION ALL
    SELECT  98765 UNION ALL
    SELECT  24680 UNION ALL
    SELECT  13579 UNION ALL
    SELECT  963852 UNION ALL
    SELECT  147258 




SELECT * 
    FROM @Table 
    WHERE CAST(SomeInt AS varchar(10)) LIKE '1%'

---- Results
1234567
13579
147258

Answer 2

SELECT DISTINCT
  CASE
    WHEN CAST(table_one.integer_col AS VARCHAR(MAX)) LIKE 'argument%'
        THEN table_one.integer_col
    WHEN table_one.varchar_col LIKE 'argument%'
        THEN table_one.varchar_col
    WHEN table_two.varchar_col LIKE 'argument%'
  END
  FROM table_one
  INNER JOIN table_two
  ON table_one.pk=table_two.fk
  WHERE
    CAST(table_one.integer_col AS VARCHAR(MAX)) LIKE 'argument%' OR
    table_one.varchar_col LIKE 'argument%' OR
    table_two.varchar_col LIKE 'argument%'