Replace values in a column for all rows-CodePudding

I have a column with entries like:

column:
156781
234762
780417

and would like to have the following:

For this I use the following query:

Select isnull(replicate('0', 10 - len(column)),'')   rtrim(column) as a from table)

However, I don't know how to replace the values in the whole column.

I already tried with:

UPDATE table
SET column= (
  Select isnull(replicate('0', 10 - len(column)),'')   rtrim(column) as columnfrom table)

But I get the following error.

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

CodePudding user response：

Usually, when we need to show values in specificity format these processes are performed using the CASE command or with other functions on the selection field list, mean without updating. In such cases, we can change our format to any format and anytime with changing functions. As dynamic fields. For example:

select id, lpad(id::text, 6, '0') as format_id from test.test_table1
order by id

Result: 

id  format_id
-------------
1   000001
2   000002
3   000003
4   000004
5   000005

Maybe you really need an UPDATE, so I wrote a sample query for an UPDATE command too.

update test.test_table1 
set 
  id = lpad(id::text, 6, '0');

CodePudding user response：

The answer to your question is going to depend on the data type of your column. If it is a text column for example VARCHAR then you can modify the value in the table. If it is a number type such as INT it is the value and not the characters which is stored.
We can also express this by saying that "0" "1" = "01" whilst 0 1 = 1.
In either case we can format the value in a query.

create table numberz(
val1 int,
val2 varchar(10));
insert into numberz values
(156781,'156781'),
(234762,'234762'),
(780417,'780417');
/* required format 
0000156781
0000234762
0000780417
*/
select * from numberz;
GO

  val1 | val2  
-----: | :-----
156781 | 156781
234762 | 234762
780417 | 780417

UPDATE numberz 
SET val1 = isnull(
  replicate('0', 
  10 - len(val1)),'') 
    rtrim(val1),
val2 = isnull(
  replicate('0', 
  10 - len(val2)),'') 
    rtrim(val2);
GO

3 rows affected

select * from numberz;
GO

  val1 | val2      
-----: | :---------
156781 | 0000156781
234762 | 0000234762
780417 | 0000780417

select isnull(
  replicate('0', 
  10 - len(val1)),'') 
    rtrim(val1)
from numberz
GO

| (No column name) |
| :--------------- |
| 0000156781       |
| 0000234762       |
| 0000780417       |

db<>fiddle here