August 29, 2011 at 7:31 pm
Check this out :
DECLARE @TabValue TABLE ( Value NVARCHAR(50) )
INSERT INTO @TabValue
SELECT '1239484848484848484'
UNION ALL SELECT ' 12345'
UNION ALL SELECT '1234567890'
UNION ALL SELECT '12345 '
UNION ALL SELECT '1234'
SELECT CASE WHEN LEN(LTRIM ( RTRIM(Value))) < 10 THEN REPLICATE('0' , 10-LEN(Value) ) + LTRIM ( RTRIM(Value))
ELSE RIGHT(LTRIM ( RTRIM(Value)),10)
END AS 'VALUE'
FROM @TabValue
August 29, 2011 at 7:48 pm
Try this newer version
DECLARE @TabValue TABLE ( Value NVARCHAR(50) )
INSERT INTO @TabValue
SELECT '1239484848484848484'
UNION ALL SELECT ' 12345'
UNION ALL SELECT '1234567890'
UNION ALL SELECT '12345 '
UNION ALL SELECT '1234'
; with cte as
(
SELECT LTRIM ( RTRIM(Value)) Trimmed_string , LEN(LTRIM ( RTRIM(Value))) Trimmed_length , LEN(Value) lgt
FROM @TabValue
)
SELECT CASE WHEN Trimmed_length < 10 AND ( lgt <= 10 OR lgt >= 10)
THEN REPLICATE('0' , 10-Trimmed_length ) + Trimmed_string
WHEN Trimmed_length < 10
THEN REPLICATE('0' , 10-lgt ) + Trimmed_string
ELSE RIGHT(Trimmed_string,10)
END AS 'VALUE'
FROM cte
August 29, 2011 at 7:50 pm
But i must say, dont pad zeros to the cast values and store. Let the values be cast to interger and stored. This will give you lot of flexibility. Padding with zeroes should best be done at the Application Layer; not recommened for storing/selecting.
My 0.2 $!!
August 29, 2011 at 8:10 pm
WOW, great stuff!
Thanks a lot for the prompt response and your help. Works great!!
August 30, 2011 at 7:54 am
simflex-897410 (8/29/2011)
WOW, great stuff!Thanks a lot for the prompt response and your help. Works great!!
Glad i could help 🙂
Have a look at my previous post also on not casting an integer as CHAR for storing.:cool:
Viewing 5 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply