SELECT based on substring or position? 1

[jimoblak]

I have cruddy data in a field...

record_id:cruddy_data
1:1211
2:1215
3:1289
4:1311
5:1315
6:1389
7:13110

These character strings are actually 2 sets of 2- or 3-character strings stuck in the same column due to bad DB design. I cannot break the strings into 2 columns as the data is used by another system.

How could I select records where the second set of numbers is greater than 75 (example: record_id 3,6 & 7)? Can I select based on string position?

- - picklefish - -
Why is everyone in this forum responding to me as picklefish?

 

[sleipnir214]

Man, that is cruddy database design.

What is the format of that record like? Is it:

any number of digits, followed by a colon, followed by two digits, followed by any number of digits?




Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!!

 

[jimoblak]

It is from a poorly designed AS400 system that has not really been structurally improved since the 1970s. It is worse than first described...

if strlen is 4,
xxyy

if strlen is 5,
xxyyy

if strlen is 6,
xxxyyy

If there is no magic SQL to select based on a substring, I will have to re-evaluate the import/export of the AS400 data to MySQL.

AS400 -> MySQL, break into two fields.
MySQL -> AS400, concatenate back to one field.

I was hoping the SQL could handle this as whatever import/export hack I create will be obsolete in a month when the AS400 system is scrapped. I didn't want to invest too much time into this.

I'm using MySQL to run a redundant system to verify data in the AS400. With my obviously limited knowledge, I've been verifying data in two days what takes the AS400 crew months to do.

I believe the original database is pulling from a flat file: separating fields by character position in a single string of 128 characters.

- - picklefish - -
Why is everyone in this forum responding to me as picklefish?

 

[sleipnir214]

The only way I can describe that schema is through the use of colorful expressions I learned in the military.


It can be done, but it will not be pretty. I use a case statement in my where clause.

Using the example data you gave in your original post, the following query:

select * from foo where
case length(substring_index(id, ':', -1))
	when 4 then substring(substring_index(id, ':', -1),3) > 75
	when 5 then substring(substring_index(id, ':', -1),3) > 75
	when 6 then substring(substring_index(id, ':', -1),4) > 75
end

returns the records you want.


Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!!

 

[jimoblak]

This SQL befuddles me slightly but it pointed me to

http://www.mysql.com/doc/en/String_functions.html

where I think I have found a solution to the problem.

What you presented returns results but when I try to mix in another condition like

WHERE `record_id` > '3'

, no results are returned. Can I not mix other conditionals here?

Thanks for the direction!

- - picklefish - -
Why is everyone in this forum responding to me as picklefish?

 

[sleipnir214]

You should be able to. Exactly what are you trying?

Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!!

 

[TonyGroves]

How about:

SELECT * FROM tbl WHERE SUBSTRING(fld FROM LENGTH(fld)/2+1)>75

 

[jimoblak]

I've been dumbing down my examples for this forum. The query I'm doing is a lot more complex. It turned out that I was trying to select several things that did not overlap... resulting in a empty return. After trimming out the extra fluff, it works fine. Thanks

I'm impressed by how fast this executes even though it is plodding through string processing. We might as well design databases with one column and humungous strings.

- - picklefish - -
Why is everyone in this forum responding to me as picklefish?