SQL search multiple fields, return one

Question

I have a query that needs to compare a value against multiple columns, and only return results from one. I keep getting a conversion error when it tries to convert a string argument into an integer, but I haven't been able to find a good way to circumvent 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 'argument%' can successfully be converted to an integer, but when it can't, the query bombs.

Answer 1

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%'

Answer 2

How about converting or casting your integer to a varchar datatype? Rather than converting your varchar search argument to an int to match the int column, convert your int column to a varchar.

Here's 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