Thread: Data conversion in query
Hello, I'm having a small difficulty that I am hopeful there is a simple solution for. Basically, I am doing a join on a column that is numeric in one database but a string in the other. Something like: ...WHERE a.numeric_zipcode=b.string_zipcode::integer... The problem is that occasionally the string zip field has some non-numeric characters. Unfortunatley, I cannot control the sanity of the string zip field. The question is if there is any way that in the same query I can filter out the rows that have non-number characters in the zip field? Thanks for any thoughts, Tad
Tad Marko <tad@tadland.net> writes: > Hello, > > I'm having a small difficulty that I am hopeful there is a simple solution > for. Basically, I am doing a join on a column that is numeric in one > database but a string in the other. Something like: > > ...WHERE a.numeric_zipcode=b.string_zipcode::integer... > > The problem is that occasionally the string zip field has some > non-numeric characters. Unfortunatley, I cannot control the sanity of the > string zip field. The question is if there is any way that in the same > query I can filter out the rows that have non-number characters in the zip > field? You should be able to use regular expressions for this. -Doug
On Thu, Mar 27, 2003 at 13:43:10 -0600, Tad Marko <tad@tadland.net> wrote: > Hello, > > I'm having a small difficulty that I am hopeful there is a simple solution > for. Basically, I am doing a join on a column that is numeric in one > database but a string in the other. Something like: > > ...WHERE a.numeric_zipcode=b.string_zipcode::integer... > > The problem is that occasionally the string zip field has some > non-numeric characters. Unfortunatley, I cannot control the sanity of the > string zip field. The question is if there is any way that in the same > query I can filter out the rows that have non-number characters in the zip > field? Why don't you compare them as strings? You can use to_char to force the numeric zip code to 5 digits.