How can I Make it 10 All the time?

Question

Post reply

How can I Make it 10 All the time?

simflex-897410

Hall of Fame

Points: 3881
More actions
August 29, 2011 at 6:00 pm

#146079

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

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

ColdCoffee SSC-Dedicated Points: 39971 More actions · Answer 1

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

ColdCoffee SSC-Dedicated Points: 39971 More actions · Answer 2

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

ColdCoffee SSC-Dedicated Points: 39971 More actions · Answer 3

ColdCoffee

SSC-Dedicated

Points: 39971

August 29, 2011 at 7:50 pm

#1374794

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 $!!