August 29, 2011 at 6:00 pm
Greetings Super Experts.
I have a userid (nvarchar(50) as defined on the db.
The userid has values from 0 to 20.
We usually grab these values an insert them into another DB.
The problem is that the requirement for inserting these into another DB is that the values *must* be 10 characters at all times.
As stated, it could be 0 and it could be 20.
What we were told is that if the the value is say, 9484, then padd it with 0s until 10 characters.
So, the sample value of 9484 should be padded to read 0000009484.
If it is more than 10 characters, say 1239484848484848484, then chop off anything in excess of 10 from back so that number should be 8484848484.
I hope I am clear and I hope I can get your generous help.
Thanks very much in advance
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 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply