CODEWITHSUNDEEP
sql-servert-sql
Alex Garcia
Alex Garcia

Reputation: 17

Iterating through a column(string) with a while loop and concatenating 0s

I made the following script/query in TSQL(to which I happen to be a begginer):

USE HIS_WEB
GO
DECLARE @NSS_LENGTH AS INT, @NSS AS VARCHAR(30)
SET @NSS_LENGTH = LEN((SELECT Folio FROM DBO.Pacientes WHERE IdPaciente=1003))
SET @NSS = (SELECT Folio FROM DBO.Pacientes WHERE IdPaciente=1003)
WHILE (@NSS_LENGTH < 18)
BEGIN
    SET @NSS = @NSS + '0' 
    SET @NSS_LENGTH = @NSS_LENGTH + 1
    --PRINT @NSS
END
SELECT @NSS AS NSS_AUTOFILL

SELECT Folio,* FROM DBO.Pacientes WHERE IdPaciente=1003

This is what I get: enter image description here

It does what is suppossed to but it has a mayor flaw. The while loop iterates through a single value. What I want is that it iterates through a column of string values and it concatenates 0s until the while loop becomes false.

Like this one: enter image description here

The @NSS variable can only store a single value. I think this is where the main issue lies. Can I store multiple values in a variable?. For the record, I tried that and got the followeing error 'Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.'.

I don't know if this is of any help but I addressed the very same task with PYTHON and succeded.

NSS = ['9413880090','97845687','94138800901M1988','5218']
for i in NSS:
    x = len(i)
    while x < 18:
        i += '0'
        x += 1
    print(i)

enter image description here

I want my TSQL code to behave similarly to my python script. Is that possible or I need to tackle this problem in a different fashion(cursors)?.

Note: I don't want to use UPDATE statements to update my string values. I just want to get the concatenation done in the resultset. Thanks in advance.

Upvotes: 0

Views: 140

Answers (1)

allmhuran
allmhuran

Reputation: 4464

select  nss_autofill = left(Folio + replicate('0', 18), 18),
        -- rest of your columns here...
from    dbo.Pacientes

The replicate() function does what it says on the tin. Given some varchar value as argument 1, and an int as argument 2, it creates a new varchar value which is arg1 repeated arg2 times.

Left then takes a varchar as the first argument, and returns the first N characters, where N is the second argument.

Upvotes: 1

Related Questions