SQL to copy value from one nvarchar field to bit fields

This is a very delicate script so I am going to rely on the experts  :)

I have an nvarchar field that holds a 11 digit number, all either 1 or 0 values, an example would be: 11001011001

I need to take the first digit and copy that value to a field called 'client_personalprofile' then the second digit to another field and so on. There are two digits I don't need to copy so we can ignore those, below its a table of the digit number from left to right and to which field name it should be copied to.

All fields are in the same table, table name is:  "Userlogin"

field1 :  client_personalprofile
field2:  client_case_geninfo
field3:  client_case_atty
field4:  NA
field5:  client_case_comments
field6:  client_case_docs
field7:  client_case_billing
field8:  client_case_steps
field9:  client_relcases
field10: client_maincase
field11:  client_case_forms
Who is Participating?

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

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.

AleksAuthor Commented:
Any ideas?
Bob LearnedCommented:
I would think that you need to use a loop and the Substring function to get what you need.
AleksAuthor Commented:
Ah ... yeah ... I was hoping for someone to post an example at the very least. I don't want to mess this one up, its delicate information.
HTML5 and CSS3 Fundamentals

Build a website from the ground up by first learning the fundamentals of HTML5 and CSS3, the two popular programming languages used to present content online. HTML deals with fonts, colors, graphics, and hyperlinks, while CSS describes how HTML elements are to be displayed.

Rainer JeschorCommented:

assuming that you need a T-SQL script targeting a SQL Server database table:
UPDATE [dbo].[Userlogin]
   SET [client_personalprofile] = SUBSTRING([Mask],1,1)
      ,[client_case_geninfo] = SUBSTRING([Mask],2,1)
      ,[client_case_atty] = SUBSTRING([Mask],3,1)
	  -- Ignore mask at position 4
      ,[client_case_comments] = SUBSTRING([Mask],5,1)
      ,[client_case_docs] = SUBSTRING([Mask],6,1)
	  ,[client_case_billing] = SUBSTRING([Mask],7,1)
	  ,[client_case_steps] = SUBSTRING([Mask],8,1)
	  ,[client_relcases] = SUBSTRING([Mask],9,1)
	  ,[client_maincase] = SUBSTRING([Mask],10,1)
	  ,[client_case_forms] = SUBSTRING([Mask],11,1)

Open in new window

where [Mask] is the nvarchar field holding the 11 digits Information (you did not mention the field name).

And if you do not have a testing Environment, then you might simply create a test table to verify the above script:
SELECT TOP 10 * INTO dbo.TESTUserlogin
FROM dbo.Userlogin

Open in new window

This generates a new table copying the first ten records.


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
Mark WillsTopic AdvisorCommented:
Rainer's script is pretty good -only missing a "WHERE" clause

As it stands, it will update every row in the table.

You can also use an INSERT statement to add a new row. Or better still, check if the target row already exists and then insert if new, or update if exists.

But he is correct, use the substring command:

substring(your_nvarchar, start_position, length)
Vadim RappCommented:
Depending on how you use that table - if nvarchar field is "primary" and 11 fields are always supposed to only reflect different positions of the nvarchar field, you can simply define them as expressions:

CREATE TABLE Userlogin(      
   col1  nvarchar(11) ,      
   client_personalprofile  AS (substring(col1,1,1),
   client_case_geninfo      AS (substring(col1,2,1),

With that, you won't have to "synchronize" 11 fields, they will always automatically show what you want.

Another, similar, way is to create a view.
AleksAuthor Commented:
Actually I want to update that same table. thanks.
AleksAuthor Commented:
This is great, thank you !
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
Query Syntax

From novice to tech pro — start learning today.