CODEWITHSUNDEEP
sqlsql-server
JaGo
JaGo

Reputation: 257

How to search between a combination of varchar and numeric values in SQL?

So, I know this is bad database design but unfortunately this is what I have to use for my job, and I'm struggling to figure it out.

I'm writing a sql query that checks if the value of a certain field is between two values.

Select * 
from TestTable 
where TestField between ValueA and ValueB

The only problem is that ValueA and ValueB and TestField can be either integers or varchars. They can either be something like 100 or something like X100. Values that start with X are greater than any integer without an X.

So, if ValueA is 100 and ValueB is 2000, I want to return all rows where Testfield is between 100 and 2000.

However, if ValueA is 100 and ValueB is X500, I want to return all integers greater than 100 and all values that start with X and the trailing number less than 500.

For example the order would be, from smallest to greatest:

1. 100
2. 500
3. 1000
4. 5000
5. X100
6. X500
7. X1000
8. X2000

So, for my question: How could I write a query that accurately searches between ValueA and ValueB?

With valueA and valueB as varchars it prevents me from accurately checking which numbers are greater.

Upvotes: 0

Views: 192

Answers (2)

John Bustos
John Bustos

Reputation: 19564

This should do it:

DECLARE
@Test TABLE
    (
         TestField VARCHAR(10)
    );

INSERT INTO
    @Test (TestField)
VALUES
    ('100'), ('500'), ('1000'), ('X10'), ('X500'), ('X3')
;

SELECT
     TestField
FROM
    @Test
ORDER BY
     CASE
         WHEN LEFT(TestField, 1) = 'X'
             THEN 1
         ELSE 0
     END
    ,CAST(REPLACE(TestField, 'X', '') AS INTEGER)

Basically, you're ordering first based upon whether it begins with an "X" or not, then removing the "X" and ordering it by its integer value.

Upvotes: 1

JNevill
JNevill

Reputation: 50064

I think it would be a good idea to handle the building of the WHERE clause on the application side in C#. 

sqlstring="SELECT * FROM table WHERE ";

If (Regex.Matches(yourstring2,@"[a-zA-Z]").Count > 0) {
    sqlstring=sqlstring + " testfield > @param1a OR testfield BETWEEN @param2a AND param2b"
} else {
    sqlstring=sqlstring + " testfield BETWEEN @param1 AND @param2"
}

Then you can bind with similar logic

yourstring1=500;
yourstring2="x5000";

If (Regex.Matches(yourstring2,@"[a-zA-Z]").Count > 0) {
    command.Parameters.Add("@param1a", SqlDbType.Int);
    command.Parameters["@param1a"].Value = yourstring1;        

    command.Parameters.Add("@param2a", SqlDbType.Varchar);
    command.Parameters["@param2a"].Value = yourstring2.substring(1,1) + "0";
    command.Parameters.Add("@param2b", SqlDbType.Varchar);
    command.Parameters["@param2b"].value = yourstring2;
} else {

    command.Parameters.Add("@param1", SqlDbType.Int);
    command.Parameters["@param1"].Value = yourstring1;

    command.Parameters.Add("@param2", SqlDbType.Int);
    command.Parameters["@param2"].value = yourstring2;
}

My C# is rusty at best, so I'm 100% certain that the syntax is off here and there is a much more elegant way to write this, but... the gist is the same. The programming language feels like the right place to do this instead of the SQL where this may be possible, but it's going to be really ugly and potentially error prone. In C# we have all the info needed and a lot of nuanced control that we are lacking on the SQL side.

And that's coming from someone that is generally berating people for trying to do things on the application side that should be done in the database.

Upvotes: 1

Related Questions