Database query - match 1 or more zeros
I have a varchar field that can (shouldn't, but can) contain a variable number of zero[0] characters. I want to return all rows where this field only contains zeros, no matter how many zeros it may contain. Due to too much holiday potluck, my lethargic brain can not produce even a glimmer of an idea of a concept for this. TIA for any suggestions or guidance. Ian ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:340104 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Database query - match 1 or more zeros
Can you cast is as a numeric value, all entries with '' should turn into 0, while anything with number becomes Null? then you simply check WHERE CAST() = 0 Regards, David McGraw Oyova Software, LLC - http://www.oyova.com On Fri, Dec 17, 2010 at 2:18 PM, Ian Skinner h...@ilsweb.com wrote: I have a varchar field that can (shouldn't, but can) contain a variable number of zero[0] characters. I want to return all rows where this field only contains zeros, no matter how many zeros it may contain. Due to too much holiday potluck, my lethargic brain can not produce even a glimmer of an idea of a concept for this. TIA for any suggestions or guidance. Ian ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:340107 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Database query - match 1 or more zeros
I think regex is the way to go for you. WHERE field = [0]* I think would work. On Fri, Dec 17, 2010 at 2:18 PM, Ian Skinner h...@ilsweb.com wrote: I have a varchar field that can (shouldn't, but can) contain a variable number of zero[0] characters. I want to return all rows where this field only contains zeros, no matter how many zeros it may contain. Due to too much holiday potluck, my lethargic brain can not produce even a glimmer of an idea of a concept for this. TIA for any suggestions or guidance. Ian ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:340109 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Database query - match 1 or more zeros
On 12/17/2010 11:41 AM, Michael Grant wrote: I think regex is the way to go for you. WHERE field = [0]* I think would work. Thanks, that was the seed of the idea I needed. WHERE REGEXP_LIKE(grower_id, '0+') I just changed it to 0+ because I wanted to match one or more zeros NOT zero or more zeros, which I expect would have matched ALL rows. ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:340110 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Database query - match 1 or more zeros
Perfect! Yeah I suck at regex. On Fri, Dec 17, 2010 at 2:55 PM, Ian Skinner h...@ilsweb.com wrote: On 12/17/2010 11:41 AM, Michael Grant wrote: I think regex is the way to go for you. WHERE field = [0]* I think would work. Thanks, that was the seed of the idea I needed. WHERE REGEXP_LIKE(grower_id, '0+') I just changed it to 0+ because I wanted to match one or more zeros NOT zero or more zeros, which I expect would have matched ALL rows. ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:340111 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Database query - match 1 or more zeros
Consider adding a caret to the start of the regex or you'll match multiple zeros anywhere in the cell. Would match 00 000 123000 12345670 On Dec 17, 2010, at 1:55 PM, Ian Skinner h...@ilsweb.com wrote: On 12/17/2010 11:41 AM, Michael Grant wrote: I think regex is the way to go for you. WHERE field = [0]* I think would work. Thanks, that was the seed of the idea I needed. WHERE REGEXP_LIKE(grower_id, '0+') I just changed it to 0+ because I wanted to match one or more zeros NOT zero or more zeros, which I expect would have matched ALL rows. ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:340126 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Database query - match 1 or more zeros
you'll match multiple zeros anywhere in the cell. I don't think that's what Ian's looking for though. ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:340127 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
