USE OF STUFF
-
Guys hi, a simple question for you...
i have a column (number) in a table that contains codes of shop. These codes range from 1 digit to 4 digits. Before loading this column to another table i want all codes to be transofrmed to 4 digit codes having zeros at the beggining. eg. if original number=0 i want to load 0000. similarly if original number is 1 i want to load 0001 and if code 11, i want to load 0011.
any one can offer any help with this, would be appreciated.
Thank you,
Dionisis
-
-
declare
@int int, @lpad intselect
@int = 31, @lpad = 4
select
right(replicate('0',@lpad)+ cast(@int as varchar(12)),@lpad)
Tim Wilkinson
"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant -
-
Dionisis
What I would suggest is leaving the column as it is, and have your client application present the data in the way you describe. That way, the data stays numeric instead of being converted to varchar or such like, making it easier and faster to filter and join on.
John
-
John I agree with you, but sitll here is my solution::::
CREATE FUNCTION LeftPadZeros(
@Amount bigint,
@Length int
)
RETURNS varchar(20)
AS
BEGIN
DECLARE @retVal varchar(20)
DECLARE @HoldVal varchar(20)
DECLARE @CurLen int
set @HoldVal = Convert(varchar(20), @Amount)
set @CurLen = Len(@HoldVal)
set @retVal = Replicate('0', @Length - @CurLen) + @HoldVal
RETURN @retVal
END
SELECT dbo.LeftPadZeros(Tab1.Column1,4) from Table1 tab1
Thanks
Brij
-
SELECT REPLACE(STR(yourcolumn,4),' ','0')
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions -
Not relevant under the described circumstances, but note odd overflow behaviour of STR():
select
str(999,2)
- ----
- **
- (1 row(s) affected)
Tim Wilkinson
"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant -
Yep... you shouldn't put 3 pounds of meat in a 2 pound wrapper, for sure.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions -
But if our hapless meat packer discovers he's been given a 2lb beef wrapper for a three pound sirloin, I doubt whether his employer will look kindly on it if he instead wraps up a couple of tangerines from his lunchbox and sends them merrily up the conveyer amongst the wrapped beef without comment.
To strain the analogy further, perhaps he needn't press the conveyer 'stop' switch - even just a warning would no doubt be appreciated by his meat stacking colleagues at the far end...
Tim Wilkinson
"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant -
Concur and now I get it... you were warning everyone of the same thing I was thinking... the requirement was for 4 digits left-padded with zeros and I'm thinking that's a very small number, nowadays. Easy to over-run and whoever uses such a thing needs to make very sure they add in some reliable limit checking code. And, the limit should be well before you need to hit that proverbial "stop" switch.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply