- Home /

I have numbers that need to be changed in order to import into an AS400 database. I need to replace the last digit of the value to indicate whether it is a positive or negative number. EX: -1299 needs to be changed to 129R; 1299 needs to be changed to 129I. I have a list of corresponding letters for numbers but not sure how this should be written. Any suggestions?

Comment

ThomasRushton

Kev Riley

KenJ

Kev Riley

KenJ

What's the list? It might help with making a more elegant routine rather than a 20 element CASE statement!

Remember, if an answer to your question is helpful, vote it up by clicking on the thumbs up symbol next to it. If the answer solves your problem, be sure to click the check box next it to identify it as the solution. Do this for all your questions.

**Answer** by ThomasRushton ·

Here's a quick & dirty attempt: use tempdb go declare @Rules TABLE (LastDigit tinyint, PlusOrMinus Char(1), Suffix char(1)) Insert into @Rules values (1, '+', 'A'), (2, '+', 'B'), (3, '+', 'C'), (4, '+', 'D'), (5, '+', 'E'), (6, '+', 'F'), (7, '+', 'G'), (8, '+', 'H'), (9, '+', 'I'), (0, '+', 'J'), (1, '-', 'Z'), (2, '-', 'Y'), (3, '-', 'X'), (4, '-', 'W'), (5, '-', 'V'), (6, '-', 'U'), (7, '-', 'T'), (8, '-', 'S'), (9, '-', 'R'), (0, '-', 'Q') declare @Numbers TABLE (Number int) INSERT INTO @Numbers SELECT TOP 10 CHECKSUM(NEWID())%1000+1000 FROM sys.all_columns UNION SELECT TOP 10 CHECKSUM(NEWID())%1000-2000 FROM sys.all_columns SELECT n.Number, coalesce('' + abs(n.Number), abs(n.Number)), left(coalesce('' + abs(n.Number), abs(n.Number)), len(coalesce('' + abs(n.Number), abs(n.Number)))-1) + r.suffix FROM @Numbers n LEFT JOIN @Rules r ON ABS(n.number) % 10 = r.LastDigit AND ( (r.PlusOrMinus = '+' AND n.Number >= 0) OR (r.PlusOrMinus = '-' AND n.Number < 0))

Copyright 2019 Redgate Software.
Privacy Policy

- Anonymous
- Sign in
- Create
- Ask a question
- Spaces
- Site Issues (NOT FOR DATABASE QUESTIONS)
- Explore
- Topics
- Questions
- Users
- Badges