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

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