T SQL code snipets

Snipets.sql

-- How to show differences between white space characters and empty string characters as sql sees string.empty as the same as a space
declare @string as varchar(10)=''

if @string= char(32) or @string= char(9) or @string= char(10)
begin
    IF DATALENGTH(@string)=0
        Print 'string.empty' --action if empty string
        else
        begin
        Print 'ALL White Space Detected' --action if only white spaces
    end
end
else
begin
Print 'NOT ALL CHARACTERS ARE WHITE SPACES' --action if not all white spaces or string.empty
end

-- Below is a simple CTE replacing a WHILE LOOP that trims white spaces and zero pads a number to 7 digits

DECLARE @TEMP TABLE
(intROWID INT IDENTITY, 
 strData nvarchar(20)
)

insert into @TEMP(strData)
values('657516 ')
,('56702')
,(' 4496')
,('371061')
,('45283 ')

select intROWID, strData as RawData from @TEMP


;WITH TrimWhiteSpacesName AS(
	SELECT
		strData
	FROM @TEMP
)
UPDATE TrimWhiteSpacesName
SET strData=TRIM(NCHAR(0x09) + NCHAR(0x20) + NCHAR(0x0D) + NCHAR(0x0A) from strData)

select intROWID, strData as RemovedWhiteSpace from @TEMP


;WITH ZeroPadding AS(
	SELECT
	strData
FROM @TEMP
)
UPDATE ZeroPadding
SET strData=RIGHT('0000000' + strData, 7)

select intROWID, strData as ZeroPadding from @TEMP