Select Query

[eluser]Sanjay Sarvaiya[/eluser]
I am on tough situation. Please help me.

I have a table with number field which contain varchar data type. we are inserting record in a format like ( <b>number/str/str/number</b> ) in this first number is increase one by one and last integer contain current year.
I want to search record From number to End number.
is it possible or not ? if yes , please guide me.

any help is appreciate.

[eluser]Diegosf93[/eluser]
I dont know if i understand very good but if what you want to do is select thats records beetween some number you can make this:

Select * from table_name where number between xxx and xxx

[eluser]Aken[/eluser]
You should normalize your database so that the records are stored individually, and not in some silly format like that. Then you wouldn't be having this problem.

[eluser]PhilTem[/eluser]
As Aken sad: You should change your table.

But anyways, it is possible.
For searching a string that starts with 'BEGIN' do

Code:

SELECT * FROM `table` WHERE `column` LIKE 'BEGIN%'

For searching a string that end with 'END' do

Code:

SELECT * FROM `table` WHERE `column` LIKE '%END'

It's basic MySQL-Syntax

[eluser]Sanjay Sarvaiya[/eluser]
Thanks for your reply.

@Aken I know that this is silly format. Thanks for your suggestion but its not possible to change db structure B'Coz of some reason.

@PhilTem your solution is right but its lengthy if some one enter for search From "001/str/str/12" To "10030/str/str/12" how can I search in light weight query.

Thanks again for trying to helping me.

[eluser]Aken[/eluser]
No query will be "light weight" for this. You'll either need an ugly SQL query, or a proper DB structure.

[eluser]Sanjay Sarvaiya[/eluser]
@aken any solution for that BCoz my client was developed many section and its not possible to change it.
If not light weight no-problem I want to output. PLZ.

[eluser]Sanjay Sarvaiya[/eluser]
I have found solution after a day of hard work and using google I got what I want.

I have search from number <b>001/SPJ/BT/09</b> to number <b>099/SPJ/BT/12</b>, In this case search query will be like this

Code:

SELECT * FROM tablename WHERE (po_number LIKE '%/SPJ/BT/09' AND SUBSTRING_INDEX( SUBSTRING_INDEX( po_number, '/' , -4 ) , '/', 1 ) >= 001 ) OR (po_number LIKE '%/SPJ/BT/12' AND SUBSTRING_INDEX( SUBSTRING_INDEX( po_number, '/' , -4 ) , '/', 1 ) <= 099) OR po_number LIKE '%/SPJ/BT/10' OR po_number LIKE '%/SPJ/BT/11'