Unique search query

[chemimpex]

I have a varchar field which contains locations like '45019A' etc. These are our bin numbers

Some of these contain multiple locations like
'40011A,45011A'.

I want to search rows which contain a range of these using
between '45277A' and '45288Z'

This query works fine when data starts with these locations but it doesn't return rows where this range of locations in the 2nd location.

What query would i write to extract this data from the range even if the location is not the 1st part of the data meaning if i search on range '45001A to 46001Z' it should return both '45025A' as well as '50001A,45022A'
Thanks in advance




If anything can go wrong, it WILL.

 

[rac2]

This query works fine..."

Post the query.

 

[SQLSister]

Inthe first place you need to change your database structure. never store multiple pieces of data in one field. You should have a related table to store this data in. Otherwise you can;t use between to search for the data you want. You would have to build a like statement to search for each one of the items you want using wildcards which will not only be painful to build dynamically (as well as less secure), it will be a performance killer as it will mean you can't use indexes to find the data. While changing the sdtructure will be painful, it is really the only viable solution if your database is going to be large in time and you will be doing this kind of serching frequently.

Questions about posting. See faq183-874
Click here to help with Hurricane Relief

 

[chemimpex]

RAC2, the query doesn't work, I don't think you have understood my questions.

SQLSister, These tables are not created by me. They are already existing tables of an ERP software. There is no chance of changing the structure.
The frequency won't be too often for this query but I need to run it now. Performance is not a problem as of now, results are required no matter how bad the performance. If you can give me some query, it would be awesome, thanks for your time.

If anything can go wrong, it WILL.

 

[thendrickson]

First SQLSister is 100% correct.

However, I was recently placed in a similar position. Sometimes you just cannot do the correct thing.

My suggestion is to do a search in this forum for "deconcantenate" I saw some code that looks like it might work for you (my requirements were a bit different since I had single records deliminted by | then



Insert the parsed values into a temp table then query the temp table.

 

[SQLDenis]

do a search for the split function

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[thendrickson]

SQLDenis

I do not believe the Split function is available in T-SQL

You can use Split in VB and VB scripting of course so I have used split in a DTS package, but I do not find Split in BOL.

Your posts are always quite helpful (as are SQLSisters and some other bright people), so I am assuming I am showing my ignorance here. could you clear this up a bit for me?

 

[SQLDenis]

faq183-3979


Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[thendrickson]

Kewl!!!!!!!

Wish I had found that in January

Thank you

 

[gmmastros]

Here's another method that may perform better under certain circumstances. thread183-1243232

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom