How to store decimal with letters and then display 1

[jsteiner87]

I have a listing of items in a database and they have section numbers that are attached to these items. For example:

1.10 Item #1
1.20 Item #2
2.24 Item #3
11.00 Item #4

I currently have these stored as a decimal. Now I need to add letters onto some of these decimals.

1.10 Item #1
1.20 Item #2
2.24 Item #3
2.24n Item #5
11.00 Item #4

If I store the items as varchar they would sort like this.

1.10 Item #1
1.20 Item #2
11.00 Item #4
2.24 Item #3
2.24n Item #5

How can I store these section numbers and have a letter in with it and sort them in a numeric order?

 

[Dweezel]

You could create a seperate field in your database table for storing the letters that you need to append.

 

[guelphdad]

change them to varchar. When you sort a varchar column you can just do

ORDER BY yourvarcharcolumn + 0

That will force correct ordering of your column.

 

[audiopro]

What does the '+ 0' do?

Keith

http://www.studiosoft.co.uk

 

[guelphdad]

The + 0 fools the database into thinking the data is in integer format it then sorts the data accordingly. Not sure it works outside of mysql.

 

[jsteiner87]

Used it in my PHP code and worked very well, thanks for the help.

 

[r937]

hey guelphdad, welcome to tek-tips, congrats for the star, i hope you get many more

;-)

r937.com | rudy.ca

 

[guelphdad]

Cheers Rudy. I think I've been here a long time ago. Saw something on your site recently and ended up here.