SQL Trim problem

[jay123]

Hello All,
Problem i am facing is , i need to add some zero's to the user input. if user inputs 012345, i need to save 00012345 (total 8 characters), which can be done by

CAST(REPLACE(STR(VarcharValue,8),' ','0') as Varchar(8))

but this code breaks when user enter's any string with alphanumeric characters. Is their any way i can do like if input has any alphanumeric character in it, ignore this replace query else do this?

just for info: whole data is database is stored in varchar(8), which can be integer value with leading 0 if less than 8 characters to make it to 8 characters OR some varchar value.

Any help will be appreciated

Regards
Jay

[jvskarthick]

Hi,

Check with the below given option.

DECLARE @STR VARCHAR(8)
SELECT @STR = '01105'
--SELECT ISNUMERIC(@STR)
SELECT CASE WHEN ISNUMERIC(@STR) = 1 THEN RIGHT(('00000000' + @STR), 8) ELSE RIGHT(('00000000' + @STR), 8) END

Thanks,
JK

[jay123]

@jvskarthick
on the spot, really appreciated.

Thanks