0

I have a list of valid states say 'dc', 'Nu', 'tN', 'VA'. I also have a list of invalid/junk states say 'aa', 'BB', 'CC'

Now I want to do an update that will update the valid states in upper case so that I can get the following updated data: 'DC', 'NU', 'TN', 'VA'

This will not work:

update states set state = upper(state) where state in ('DC', 'NU', 'TN', 'VA');

Any way to update small case to CAPS if its valid one?

UPDATE:

I can have a particular state in any case e.g. 'DC' or 'dc' or 'dC' or 'Dc'. Obviously I can do something like update states set state = upper(state) where state in ('DC', 'dc', 'dC', 'Dc'). But its a extra mentioning multiple times for the same state. Isn't there any elegant way?

sjain
  • 23,126
  • 28
  • 107
  • 185
  • 3
    Have you tried `WHERE Upper(State) IN (...`? – Siyual Sep 01 '17 at 14:30
  • 1
    Honestly. This feels like a trick question. – VynlJunkie Sep 01 '17 at 14:33
  • see my query in question. It is the same that you mentioned. But I can have a particular state in any case e.g. 'DC' or 'dc' or 'dC' or 'Dc'. Obviously I can do something like `update states set state = upper(state) where state in ('DC', 'dc', 'dC', 'Dc')`. But its a extra mentioning multiple times for the same state. Isn't there any elegant way? – sjain Sep 01 '17 at 14:34
  • It is not the same. The suggestion from @Siyual will avoid the need to mention a state multiple times since he is making the state column uppercase to compare with only one string ocurrence so no matther if you have `'DC', 'dc', 'dC', 'Dc'` using upper you only need only one `DC` in the IN statement list. – Jorge Campos Sep 01 '17 at 14:37

1 Answers1

4

There's no need to do in ('DC', 'dc', 'dC', 'Dc'), etc. You can force the column to be Upper() and only compare those values:

update states 
set    state = upper(state) 
where  Upper(state) in ('DC', 'NU', 'TN', 'VA');
Siyual
  • 16,415
  • 8
  • 44
  • 58
  • Seems like a reasonable solution. Checking it. – sjain Sep 01 '17 at 14:38
  • @Siyual This is a fine solution because we know there's no orthographic issues because this is being applied to American state abbreviations. However there can be issues with this solution in general. See the answers to [this question](https://stackoverflow.com/q/7005302/119477) that use ILIKE or the citext module – Conrad Frix Sep 01 '17 at 14:55
  • Just be carefull if you want to use an index on the state column to speed up your search... In that case, you should create an index on upper(state). – Arkhena Sep 01 '17 at 15:24
  • To add onto Siyual's response, if you're going to be referencing this "valid states list" multiple times, I would create a table for it and just join against the newly created table. That way you can modify the valid states and make sure that all your different queries that reference said table always produce the same results rather than needing to go in and remember where you hard-coded the list each time. – b-poe Sep 01 '17 at 17:32