Posted by Erland Sommarskog on 04/28/06 10:27

Munno (patelroshan@gmail.com) writes:
> I am not so proficient in SQL and seek your help.
>
> I have a column by the name of Mask in a table, which has text eg.
> (YYYYYYYNNNNYYYYYYYYYNNYYYY). I wanted to update one particular value
> in that text. How would my update statement look like?
>
> Below is my select statement.
>
> select user, substring(mask, 50, 1) Authorisation from users where type
>= 1 order by Authorisation desc
>
> Below statement doesn't work.
>
> update users set substring(mask, 50, 1) = 'Y' where user = 'me'

This is not Perl, so you can't do this.

Since you know substring, you might have figured this out on your own
already:

UPDATE users
SET mask = substring(mask, 1, @bitno -1) + @newval +
substring(mask, @bitno + 1, len(mask))
WHERE user = 'me'

I should add that this sort of mask is dubious from a design perspective.
It may be a lot better to make the various bits column in a table instead.



--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]