Type Comparisons in SQL

Question

I've got the following bit of code as part of a SQL Query:

INSERT INTO [Database]  
 SELECT DISTINCT @ssId 
   FROM [Document_Map] 
  WHERE (LabelId IN (SELECT Tokens 
                       FROM StringSplitter(@sortValue, '|', 1))

The code works fine as long as @SortValue is an integer, (LabelId is an int as well) or integers separated by the delimiter character (e.g., SortValue 420|421| compares against 420 and 421). However, I'm adding functionality which involves non-integer values for @sortValue. E.g.: 420|ABC|421| should compare against 420, ABC, and 421.

No worries, I thought. I'll just modify the last line of that query!

(LabelId IN (SELECT Tokens FROM StringSplitter(@sortValue, '|', 1)) OR 
StringId IN (SELECT Tokens FROM StringSplitter(@sortValue, '|', 1)))

Unfortunately, whenever I feed in @sortValue with characters in it, I get an error. It never made it to the far side of the or.

After much mucking around, I finally determined that SQL is casting the string results from the StringSplitter function to compare against LabelId. No problem when the string results were guaranteed to contain only numeric characters, but SQL refuses to cast (understandably) the non-numeric string to an int, throwing out an error.

What's the easiest way to get around this error while maintaining desired functionality? Due to database interaction, I am not sure if changing the type of LabelId is a viable option.

Answer 1

Expanding on what DCP said, try this code:

(Convert(varchar(32),LabelId) IN (SELECT Tokens FROM StringSplitter(@sortValue, '|', 1)) OR 
StringId IN (SELECT Tokens FROM StringSplitter(@sortValue, '|', 1)))

Just watch out for performance problems, casting in a WHERE clause can cause problems...

Answer 2

For your functional problem, try ISNUMERIC:

; WITH data (Foo) AS (
             SELECT '1'
   UNION ALL SELECT 'abc'
)
SELECT
   *
FROM data
WHERE ISNUMERIC(Foo) = 1;

In addition, if your Split function uses any other tables (like a common numbers table), move the split part out of your SELECT statement and store the results in a temp table. Calling a table valued function with schema-binding in the WHERE part of a statement can (and often does) cause a call of the function for each row of your table.

Greets Flo

Answer 3

I have suggestions but also questions...

The underlying reason is Datatype precedence rules.

What I don't get is why compare a numeric column against string data? Why send non-numeric data for comparison?

If int LabelID "24" cast to string was compared to "024" string, it would fail. String "024" becomes "24" when cast to int though and would match. What behavior do you want and expect?

I can understand fixing a broken schema or tidying data using ISNUMERIC, but you appear to be deliberately sending strings.

Anyhow, you're fixes are

• change the column type to match what you send
• CAST the column
• filter strings out from the comparison either in string split (eg "StringSplitterToInt") or in the client

I'd suggest number 1 to reflect your model and actual data or number 3 to fix your code...