[thelist] SQL Tip

[Burhan Khalid]

Hey All :

	A while back, I think someone had a question about a VARCHAR field and
searching (or am I just dreaming this stuff?). Anyhow, I recently faced a
similar issue, and here is the bit of SQL that did the trick for me.
Hopefully it works for someone else too.

<tip type="SQL" author="Burhan Khalid">

	This is an SQL query to find (or compare against) numerical data in a
	VARCHAR field :

	SELECT myColOne,myColTwo, ... myCol/n/ FROM myTABLE WHERE
	CASE WHEN ISNUMERIC(myVarCharColValue) = 1 THEN
	CAST(myVarCharColValue AS int) ELSE Null END
	BETWEEN 'minimum' AND 'maximum'

Explanation / Legend :

	*myColOne,myColTwo, ... myCol/n/*
		This could be replaced by *, or just pick your columns. Make sure to
		include your VARCHAR column (duh).

	*myVarCharColValue*
		This hold the suspect data. This is the column name for your VARCHAR
		column.

	*CAST(myVarCharColValue AS int)*
		This is what does the magic. If your value returned is actually a
		number, then this will convert (cast, or for those familiar with
		programming, typecast)it into an INT (which is what I choose in this
		case).

	*BETWEEN 'minmum' AND 'maximum'*
		This gives it a range to search for.
</tip>