MS SQL Selecting a column value based on a Select statement

I am trying to verify that the State column in the Customer table is a valid state and if not force the state value of 'MI' in the State column.
Here is the code I currently have and I am trying to do a select in a Case statement and not certain if this is the correct way to do it or not.

---  Table @State with the valid state abbreviations.
DECLARE @State TABLE(State CHAR(2))

INSERT @State (State)
VALUES
            ('AK'),('AL'),('AR'),('AZ'),('CA'),('CO'),('CT'),('DE'),('FL'),('GA')
            ,('HI'),('IA'),('ID'),('IL'),('IN'),('KS'),('KY'),('LA'),('MA'),('MD')
            ,('ME'),('MI'),('MN'),('MO'),('MS'),('MT'),('NC'),('ND'),('NE'),('NH')
            ,('NJ'),('NM'),('NV'),('NY'),('OH'),('OK'),('OR'),('PA'),('RI'),('SC')
            ,('SD'),('TN'),('TX'),('UT'),('VA'),('VT'),('WA'),('WI'),('WV'),('WY')
            ,('DC'),('ON')


SELECT StreetAddress
      ,Phone
      ,City
      ,CASE WHEN (SELECT s.[state] FROM @State s WHERE s.[State] = c.[State]) = 'NULL' THEN 'MI'
                  WHEN LEN(RTRIM(c.[State])) = 0 THEN 'MI'
                  ELSE UPPER(c.[State])
            END AS [State]
      ,Zip
        ,AccountNumber
      ,FirstName
      ,LastName
      ,HomeStoreNumber AS PSP_HomeStore
  FROM dbo.Customer c
 
With the CASE statement above I am getting some of the Customer States to be 'N', 'M', '4', '6I' etc.  Where am I going wrong and is the CASE statement the best way to handle this?

Thanks,
John
j_heckAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim HornMicrosoft SQL Server Data DudeCommented:
>if not force the state value of 'MI' in the State column.
Are you trying to overwrite the values in the table (i.e. UPDATE), or just select a different value (i.e. SELECT)?

If SELECT, give this a whirl...

SELECT StreetAddress
       ,Phone
       ,City
       ,CASE st.state WHEN NULL THEN 'MI' ELSE c.state END
       ,Zip 
         ,AccountNumber
       ,FirstName
       ,LastName
       ,HomeStoreNumber AS PSP_HomeStore
FROM dbo.Customer c
   LEFT JOIN @state st ON c.state = st.state

Open in new window


Translation:  Left Join on your state table so that all customer rows are returned, and if there is not a matching state value (i.e. st.state IS NULL) then display 'MI', if there is a matching state value display that (i.e. c.state)

btw I have an article called SQL Server CASE Solutions that would be a good read.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jim HornMicrosoft SQL Server Data DudeCommented:
The inverse of the above, which should be given to somebody to correct all of these bad state codes, is...
SELECT c.*
FROM dbo.Customer c
   -- LEFT means return all rows from dbo.Customer ...
   LEFT JOIN @state st ON c.state = st.state
-- ... which does not have a matching State code to a table of valid states
WHERE st.state IS NULL

Open in new window

F PCommented:
Is it State.State or State.state? You use both. How are you collating?

...

SELECT s.[state] FROM @State s WHERE s.[State] ....
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

j_heckAuthor Commented:
Jim, I have tried both and I am not getting the results I am looking for.

Let me try and make it clearer what I am looking for in the result set.  Currently the values in the column Customer.State can be a valid State (ie. MI, IL, KY, FL, etc), or it could be a blank/null, or it could be any other value of 1 or 2 characters (ie. NL, 40, OP, O5, TT, Z, X, 3, etc).  What needs to be returned in the result set is a valid State Abbreviation based on a State table.  The State table value of State.State is one of the 50 states plus District of Columbia (DC) & Ontario Canada (ON).  So the data returned from the Customer.State column needs to be a valid state found in the State.State table or if anything else the value is defaulted to 'MI'.  But I want all of the Customers in the results set with a valid State value.

Does this make more sense?

Thanks,
John
F PCommented:
JOIN won't work? Then check the value of the column if it exists?

http://www.mssqltips.com/sqlservertip/1667/sql-server-join-example/
Jim HornMicrosoft SQL Server Data DudeCommented:
Getting there.  So if there are 100 customers, say 90 with valid values in the State table and 10 that are not, would the final result set contain 90 or 100 rows?

>But I want all of the Customers in the results set with a valid State value.
>...or if anything else the value is defaulted to 'MI'.
The above implies 100, with the 10 oddballs showing 'MI', which is the comment I provided.

Perhaps poviding us with a more detailed data mockup would help.
j_heckAuthor Commented:
The final result set would contain 100 rows.  Of the 10 that are not valid states (as mentioned in the previous post) the state value would be 'MI'.
j_heckAuthor Commented:
Frank, the LEFT OUTER JOIN and checking for NULL doesn't work.  If the Customer.State is blank then it still returns a blank value for State in the result set.
F PCommented:
I'm not sure what system you're using other than SQL, but I was saying to parse the replacement of blank records for replacement/defaults in the logic of the code surrounding the query. Is that not an option to use IF THEN ELSE conditional statements in the language receiving the result set?
Jim HornMicrosoft SQL Server Data DudeCommented:
A sample data set would be good, because based on the requirements of this question my code is a correct answer, so there has to be something else going on here.
j_heckAuthor Commented:
Frank, I am only using MS SQL.  The Customer table is an SQL table and the State table is temp table using the DECLARE statement and then inserting the individual State abbreviations.  We are importing the data from the Customer table into another SQL table/database and the State column needs to be a valid state if it is not then the state column receives the default value of 'MI'.  The value in the Customer.State column can be a valid state abbreviation already, any combination (up to 2 characters) of numbers, special characters, and/or letters or blank/null.
j_heckAuthor Commented:
Jim,  the only condition that is not getting fixed with the code you shared is Customer.State (c.State) columns that are blank or space filled.
j_heckAuthor Commented:
Jim, here is the CASE Statement that worked:

       ,CASE WHEN st.state IS NULL THEN 'MI'
                  WHEN LEN(RTRIM(c.State)) = 0 THEN 'MI'
                  ELSE c.State
                  END
Jim HornMicrosoft SQL Server Data DudeCommented:
again, need a sample data set, because if there is a match between the two tables then it will display c.State, so the only valid explanation I'm seeing is if the state table has blanks or is space filled.
Jim HornMicrosoft SQL Server Data DudeCommented:
>WHEN LEN(RTRIM(c.State)) = 0 THEN 'MI'
Wonder if any of your customer.State values have a trailing space that should be deleted.  
Run this to find out, and it's also worth checking whatever adds data to this table to see if it's validating for trailing spaces:

SELECT *  FROM customer  WHERE RIGHT(State, 1) = ' '

Thanks for the grade.  Good luck with your project.
j_heckAuthor Commented:
Thanks for the information Jim.  I will look into that.  The problem with data is that it is entered into a system out of my control and 250+ locations enter their own data.

Not a problem with the grade.  You got me thinking in the right direction.  For some reason I didn't think of that but knew about the Left Outer Join.  I was stuck in my head with another solution, doing a Select for the column results, that I could not move off of so you really helped out.  Thanks again.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.