CODEWITHSUNDEEP
sqlsql-server
Antoine Pelletier
Antoine Pelletier

Reputation: 3326

STUFF function sql returns null?

I have a specific column in a table, it shall contains only numbers in Nvarchar that have a length of 3. Unfortunately, some users wrote '12' but they should have written '012'. There were not enough validation at the time.

I need to fix that. Here is the logic I used :

UPDATE [Mandats_Approvisionnement].[dbo].[ARTICLE_ECOLE] 
SET [UNIT_ADM] = STUFF(UNIT_ADM, 0, 0, '0') 
WHERE LEN(UNIT_ADM) = 2;

The error goes like :

Cannot insert the value NULL into column 'UNIT_ADM', table 'Mandats_Approvisionnement.dbo.ARTICLE_ECOLE'; column does not allow nulls. UPDATE fails.

I can't see where the problem is, I verified and all the records contain at least 2 characters, so the STUFF function cannot returns null as there are no NULL records in that table column [unit_adm]... How do I make it work ?

Upvotes: 2

Views: 2152

Answers (2)

Sean Lange
Sean Lange

Reputation: 33581

You could make this simpler by using 

right('0' + UNIT_ADM, 3)

instead of stuff.

Upvotes: 1

jpw
jpw

Reputation: 44921

It should be stuff(UNIT_ADM,1,0,'0') as stuff returns null if the start position is 0.

Citing the documentation:

If the start position or the length is negative, or if the starting position is larger than length of the first string, a null string is returned. If the start position is 0, a null value is returned.

Upvotes: 7

Related Questions