[OT] can you define a field as varchar but index as integer/decimal

classic Classic list List threaded Threaded
8 messages Options
Reply | Threaded
Open this post in threaded view
|

[OT] can you define a field as varchar but index as integer/decimal

Tami Williams
Thanks in advance.

I don't know if this is possible and am trying to find out if I can do  
the following:

- data looks like this  100.00, 33.20, 50.04, 60.19, blank, and 0.00


- I need to store it as varchar b/c  when the field is empty I do A,  
when its 0 I do B and when its not empty/0 I do C

- I also need to be able to sort in ascending and descending order,  
but if I define the field as decimal then I can't distinguish between  
0 and blank b/c when data is imported (data is always imported) all  
the blanks become 0s


My only thought is to create 2 copies of this field - one defined as  
varchar (for conditionals and display) and the other (for sorting  
only) defined as decimal.

Any other ideas?  Can you tell MySQL to index your varchar field as  
though its a decimal and then when you say sort by that field it sort  
in asc/desc decimal order vs. alpha?

Thanks in advance.

#############################################################
This message is sent to you because you are subscribed to
  the mailing list <[hidden email]>.
To unsubscribe, E-mail to: <[hidden email]>
To switch to the DIGEST mode, E-mail to <[hidden email]>
To switch to the INDEX mode, E-mail to <[hidden email]>
Send administrative queries to  <[hidden email]>

Reply | Threaded
Open this post in threaded view
|

Re: [OT] can you define a field as varchar but index as integer/decimal

Trevor Borgmeier
Does this work...

SELECT * FROM your_table ORDER BY CAST(your_data_field, DECIMAL(5,2)) ASC;

or something like that?  not sure what a long comma delimited string
would end up looking like to MySQL when cast as a decimal though.

Rather than a comma delimited list in a field, would it make sense to
have the field separated out into a related table?

-Trevor


on 11/30/10 4:16 PM Tami Williams wrote:

> Thanks in advance.
>
> I don't know if this is possible and am trying to find out if I can do
> the following:
>
> - data looks like this  100.00, 33.20, 50.04, 60.19, blank, and 0.00
>
>
> - I need to store it as varchar b/c  when the field is empty I do A,
> when its 0 I do B and when its not empty/0 I do C
>
> - I also need to be able to sort in ascending and descending order,
> but if I define the field as decimal then I can't distinguish between
> 0 and blank b/c when data is imported (data is always imported) all
> the blanks become 0s
>
>
> My only thought is to create 2 copies of this field - one defined as
> varchar (for conditionals and display) and the other (for sorting
> only) defined as decimal.
>
> Any other ideas?  Can you tell MySQL to index your varchar field as
> though its a decimal and then when you say sort by that field it sort
> in asc/desc decimal order vs. alpha?
>
> Thanks in advance.
>
> #############################################################
> This message is sent to you because you are subscribed to
>  the mailing list <[hidden email]>.
> To unsubscribe, E-mail to: <[hidden email]>
> To switch to the DIGEST mode, E-mail to
> <[hidden email]>
> To switch to the INDEX mode, E-mail to <[hidden email]>
> Send administrative queries to <[hidden email]>
>
>

#############################################################
This message is sent to you because you are subscribed to
  the mailing list <[hidden email]>.
To unsubscribe, E-mail to: <[hidden email]>
To switch to the DIGEST mode, E-mail to <[hidden email]>
To switch to the INDEX mode, E-mail to <[hidden email]>
Send administrative queries to  <[hidden email]>

Reply | Threaded
Open this post in threaded view
|

Re: [OT] can you define a field as varchar but index as integer/decimal

Tami Williams
In reply to this post by Tami Williams
I think I found the answer:

http://www.linuxweblog.com/blogs/sandip/20060126/sorting-varchar-data-mysql

SELECT column FROM table_name ORDER BY column+0;


On Nov 30, 2010, at 5:16 PM, Tami Williams wrote:

> Thanks in advance.
>
> I don't know if this is possible and am trying to find out if I can  
> do the following:
>
> - data looks like this  100.00, 33.20, 50.04, 60.19, blank, and 0.00
>
>
> - I need to store it as varchar b/c  when the field is empty I do A,  
> when its 0 I do B and when its not empty/0 I do C
>
> - I also need to be able to sort in ascending and descending order,  
> but if I define the field as decimal then I can't distinguish  
> between 0 and blank b/c when data is imported (data is always  
> imported) all the blanks become 0s
>
>
> My only thought is to create 2 copies of this field - one defined as  
> varchar (for conditionals and display) and the other (for sorting  
> only) defined as decimal.
>
> Any other ideas?  Can you tell MySQL to index your varchar field as  
> though its a decimal and then when you say sort by that field it  
> sort in asc/desc decimal order vs. alpha?
>
> Thanks in advance.
>
> #############################################################
> This message is sent to you because you are subscribed to
> the mailing list <[hidden email]>.
> To unsubscribe, E-mail to: <[hidden email]>
> To switch to the DIGEST mode, E-mail to <[hidden email]
> >
> To switch to the INDEX mode, E-mail to <[hidden email]
> >
> Send administrative queries to  <[hidden email]>
>


#############################################################
This message is sent to you because you are subscribed to
  the mailing list <[hidden email]>.
To unsubscribe, E-mail to: <[hidden email]>
To switch to the DIGEST mode, E-mail to <[hidden email]>
To switch to the INDEX mode, E-mail to <[hidden email]>
Send administrative queries to  <[hidden email]>

Reply | Threaded
Open this post in threaded view
|

Re: [OT] can you define a field as varchar but index as integer/decimal

Tami Williams
In reply to this post by Trevor Borgmeier
trevor,

the data is 1 thing per field 100.00 OR 33.20 OR 50.04 OR 60.19 OR  
blank OR 0.00 not all those values together in one field.

My bad.

On Nov 30, 2010, at 5:34 PM, Trevor Borgmeier wrote:

> Does this work...
>
> SELECT * FROM your_table ORDER BY CAST(your_data_field,  
> DECIMAL(5,2)) ASC;
>
> or something like that?  not sure what a long comma delimited string  
> would end up looking like to MySQL when cast as a decimal though.
>
> Rather than a comma delimited list in a field, would it make sense  
> to have the field separated out into a related table?
>
> -Trevor
>
>
> on 11/30/10 4:16 PM Tami Williams wrote:
>> Thanks in advance.
>>
>> I don't know if this is possible and am trying to find out if I can  
>> do the following:
>>
>> - data looks like this  100.00, 33.20, 50.04, 60.19, blank, and 0.00
>>
>>
>> - I need to store it as varchar b/c  when the field is empty I do  
>> A, when its 0 I do B and when its not empty/0 I do C
>>
>> - I also need to be able to sort in ascending and descending order,  
>> but if I define the field as decimal then I can't distinguish  
>> between 0 and blank b/c when data is imported (data is always  
>> imported) all the blanks become 0s
>>
>>
>> My only thought is to create 2 copies of this field - one defined  
>> as varchar (for conditionals and display) and the other (for  
>> sorting only) defined as decimal.
>>
>> Any other ideas?  Can you tell MySQL to index your varchar field as  
>> though its a decimal and then when you say sort by that field it  
>> sort in asc/desc decimal order vs. alpha?
>>
>> Thanks in advance.
>>
>> #############################################################
>> This message is sent to you because you are subscribed to
>> the mailing list <[hidden email]>.
>> To unsubscribe, E-mail to: <[hidden email]>
>> To switch to the DIGEST mode, E-mail to <[hidden email]
>> >
>> To switch to the INDEX mode, E-mail to <[hidden email]
>> >
>> Send administrative queries to <[hidden email]>
>>
>>
>
> #############################################################
> This message is sent to you because you are subscribed to
> the mailing list <[hidden email]>.
> To unsubscribe, E-mail to: <[hidden email]>
> To switch to the DIGEST mode, E-mail to <[hidden email]
> >
> To switch to the INDEX mode, E-mail to <[hidden email]
> >
> Send administrative queries to  <[hidden email]>
>


#############################################################
This message is sent to you because you are subscribed to
  the mailing list <[hidden email]>.
To unsubscribe, E-mail to: <[hidden email]>
To switch to the DIGEST mode, E-mail to <[hidden email]>
To switch to the INDEX mode, E-mail to <[hidden email]>
Send administrative queries to  <[hidden email]>

Reply | Threaded
Open this post in threaded view
|

Re: [OT] can you define a field as varchar but index as integer/decimal

Brad Lindsay-2
In reply to this post by Tami Williams
On Nov 30, 2010, at 5:16 PM, Tami Williams wrote:
> - I need to store it as varchar b/c  when the field is empty I do A, when its 0 I do B and when its not empty/0 I do C
>
> - I also need to be able to sort in ascending and descending order, but if I define the field as decimal then I can't distinguish between 0 and blank b/c when data is imported (data is always imported) all the blanks become 0s

Can't you store it as a decimal, but allow it to be NULL? So the empty case would be when it's NULL. If you then set the default for that field to be NULL, it should get set to NULL when you import the blank data.

Brad
#############################################################
This message is sent to you because you are subscribed to
  the mailing list <[hidden email]>.
To unsubscribe, E-mail to: <[hidden email]>
To switch to the DIGEST mode, E-mail to <[hidden email]>
To switch to the INDEX mode, E-mail to <[hidden email]>
Send administrative queries to  <[hidden email]>

Reply | Threaded
Open this post in threaded view
|

Re: [OT] can you define a field as varchar but index as integer/decimal

Trevor Borgmeier
In reply to this post by Tami Williams
Hmmm... couldn't it be a decimal field using null as your "blank" rather
than a varchar?

-Trevor



on 11/30/10 4:40 PM Tami Williams wrote:

> trevor,
>
> the data is 1 thing per field 100.00 OR 33.20 OR 50.04 OR 60.19 OR
> blank OR 0.00 not all those values together in one field.
>
> My bad.
>
> On Nov 30, 2010, at 5:34 PM, Trevor Borgmeier wrote:
>
>> Does this work...
>>
>> SELECT * FROM your_table ORDER BY CAST(your_data_field, DECIMAL(5,2))
>> ASC;
>>
>> or something like that?  not sure what a long comma delimited string
>> would end up looking like to MySQL when cast as a decimal though.
>>
>> Rather than a comma delimited list in a field, would it make sense to
>> have the field separated out into a related table?
>>
>> -Trevor
>>
>>
>> on 11/30/10 4:16 PM Tami Williams wrote:
>>> Thanks in advance.
>>>
>>> I don't know if this is possible and am trying to find out if I can
>>> do the following:
>>>
>>> - data looks like this  100.00, 33.20, 50.04, 60.19, blank, and 0.00
>>>
>>>
>>> - I need to store it as varchar b/c  when the field is empty I do A,
>>> when its 0 I do B and when its not empty/0 I do C
>>>
>>> - I also need to be able to sort in ascending and descending order,
>>> but if I define the field as decimal then I can't distinguish
>>> between 0 and blank b/c when data is imported (data is always
>>> imported) all the blanks become 0s
>>>
>>>
>>> My only thought is to create 2 copies of this field - one defined as
>>> varchar (for conditionals and display) and the other (for sorting
>>> only) defined as decimal.
>>>
>>> Any other ideas?  Can you tell MySQL to index your varchar field as
>>> though its a decimal and then when you say sort by that field it
>>> sort in asc/desc decimal order vs. alpha?
>>>
>>> Thanks in advance.
>>>
>>> #############################################################
>>> This message is sent to you because you are subscribed to
>>> the mailing list <[hidden email]>.
>>> To unsubscribe, E-mail to: <[hidden email]>
>>> To switch to the DIGEST mode, E-mail to
>>> <[hidden email]>
>>> To switch to the INDEX mode, E-mail to
>>> <[hidden email]>
>>> Send administrative queries to <[hidden email]>
>>>
>>>
>>
>> #############################################################
>> This message is sent to you because you are subscribed to
>> the mailing list <[hidden email]>.
>> To unsubscribe, E-mail to: <[hidden email]>
>> To switch to the DIGEST mode, E-mail to
>> <[hidden email]>
>> To switch to the INDEX mode, E-mail to <[hidden email]>
>> Send administrative queries to <[hidden email]>
>>
>
>
> #############################################################
> This message is sent to you because you are subscribed to
>  the mailing list <[hidden email]>.
> To unsubscribe, E-mail to: <[hidden email]>
> To switch to the DIGEST mode, E-mail to
> <[hidden email]>
> To switch to the INDEX mode, E-mail to <[hidden email]>
> Send administrative queries to <[hidden email]>
>
>

#############################################################
This message is sent to you because you are subscribed to
  the mailing list <[hidden email]>.
To unsubscribe, E-mail to: <[hidden email]>
To switch to the DIGEST mode, E-mail to <[hidden email]>
To switch to the INDEX mode, E-mail to <[hidden email]>
Send administrative queries to  <[hidden email]>

Reply | Threaded
Open this post in threaded view
|

Re: [OT] can you define a field as varchar but index as integer/decimal

Ke Carlton-3
In reply to this post by Tami Williams
Simply use a decimal column and allow null values. When importing ensure "" is NULL instead...

Ke - sent from a phone

On 30 Nov 2010, at 22:16, Tami Williams <[hidden email]> wrote:

> Thanks in advance.
>
> I don't know if this is possible and am trying to find out if I can do the following:
>
> - data looks like this  100.00, 33.20, 50.04, 60.19, blank, and 0.00
>
>
> - I need to store it as varchar b/c  when the field is empty I do A, when its 0 I do B and when its not empty/0 I do C
>
> - I also need to be able to sort in ascending and descending order, but if I define the field as decimal then I can't distinguish between 0 and blank b/c when data is imported (data is always imported) all the blanks become 0s
>
>
> My only thought is to create 2 copies of this field - one defined as varchar (for conditionals and display) and the other (for sorting only) defined as decimal.
>
> Any other ideas?  Can you tell MySQL to index your varchar field as though its a decimal and then when you say sort by that field it sort in asc/desc decimal order vs. alpha?
>
> Thanks in advance.
>
> #############################################################
> This message is sent to you because you are subscribed to
> the mailing list <[hidden email]>.
> To unsubscribe, E-mail to: <[hidden email]>
> To switch to the DIGEST mode, E-mail to <[hidden email]>
> To switch to the INDEX mode, E-mail to <[hidden email]>
> Send administrative queries to  <[hidden email]>
>

#############################################################
This message is sent to you because you are subscribed to
  the mailing list <[hidden email]>.
To unsubscribe, E-mail to: <[hidden email]>
To switch to the DIGEST mode, E-mail to <[hidden email]>
To switch to the INDEX mode, E-mail to <[hidden email]>
Send administrative queries to  <[hidden email]>

Reply | Threaded
Open this post in threaded view
|

Re: [OT] can you define a field as varchar but index as integer/decimal

Tami Williams-3
Yes, that works... my bad. Thanks all.

On Nov 30, 2010, at 6:22 PM, Ke wrote:

> Simply use a decimal column and allow null values. When importing  
> ensure "" is NULL instead...
>
> Ke - sent from a phone
>
> On 30 Nov 2010, at 22:16, Tami Williams <[hidden email]> wrote:
>
>> Thanks in advance.
>>
>> I don't know if this is possible and am trying to find out if I can  
>> do the following:
>>
>> - data looks like this  100.00, 33.20, 50.04, 60.19, blank, and 0.00
>>
>>
>> - I need to store it as varchar b/c  when the field is empty I do  
>> A, when its 0 I do B and when its not empty/0 I do C
>>
>> - I also need to be able to sort in ascending and descending order,  
>> but if I define the field as decimal then I can't distinguish  
>> between 0 and blank b/c when data is imported (data is always  
>> imported) all the blanks become 0s
>>
>>
>> My only thought is to create 2 copies of this field - one defined  
>> as varchar (for conditionals and display) and the other (for  
>> sorting only) defined as decimal.
>>
>> Any other ideas?  Can you tell MySQL to index your varchar field as  
>> though its a decimal and then when you say sort by that field it  
>> sort in asc/desc decimal order vs. alpha?
>>
>> Thanks in advance.
>>
>> #############################################################
>> This message is sent to you because you are subscribed to
>> the mailing list <[hidden email]>.
>> To unsubscribe, E-mail to: <[hidden email]>
>> To switch to the DIGEST mode, E-mail to <[hidden email]
>> >
>> To switch to the INDEX mode, E-mail to <[hidden email]
>> >
>> Send administrative queries to  <[hidden email]>
>>
>
> #############################################################
> This message is sent to you because you are subscribed to
>  the mailing list <[hidden email]>.
> To unsubscribe, E-mail to: <[hidden email]>
> To switch to the DIGEST mode, E-mail to <[hidden email]
> >
> To switch to the INDEX mode, E-mail to <[hidden email]
> >
> Send administrative queries to  <[hidden email]>
>


#############################################################
This message is sent to you because you are subscribed to
  the mailing list <[hidden email]>.
To unsubscribe, E-mail to: <[hidden email]>
To switch to the DIGEST mode, E-mail to <[hidden email]>
To switch to the INDEX mode, E-mail to <[hidden email]>
Send administrative queries to  <[hidden email]>