'Add' to a character.

Question

Post reply

'Add' to a character.

saivko

SSC Eights!

Points: 898
More actions
August 7, 2009 at 9:04 am

#72406

What is an elegant way to Select +1 to a nchar that will always have alpha characters? while persevering the zero's
The description above explains what I'm thinking:
000021 + 1 = 000022;
000099 + 1 = 000100;

Viewing 15 posts - 1 through 15 (of 32 total)

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

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 1

THe only way thatI can think to do it is to convert it to an int and then reformat it.

SELECT NewValue = RIGHT('00000'+(CONVERT(nchar(5),CONVERT(int,'00021')+1),5)

CEWII

GSquared SSC Guru Points: 260824 More actions · Answer 2

Cast to Int, do the math, recast back to varchar and add the leading zeroes back on.

declare @String varchar(10), @Number int;

--

select @String = '000009', @Number = 1;

--

select right('0000000000' + cast(cast(@String as int) + @Number as varchar(10)), 10);

Note: I just typed that in and haven't tested the script. Might have typos. The concept works.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Lowell SSC Guru Points: 323518 More actions · Answer 3

elegant way? not really...you've got to convert to int, add the value, then convert the value back to varchar/nvarchar, then you have to add the preceeeding zeros:

SELECT RIGHT('000000' + convert(varchar(30), convert(int,000021) + 1),6)

whenever i need a column with preceeding zeros like that, i either use a view witht he format in it, or i leave the column as an int, and use a calculated column for the formatted version with preceeding zeros:

Alter Table MyTable Add MyCalculatedColumn As RIGHT('000000' + convert(varchar(30), MyoriginalColumn),6)

saivko (8/7/2009)
What is an elegant way to Select +1 to a nchar that will always have alpha characters? while persevering the zero's
The description above explains what I'm thinking:
000021 + 1 = 000022;
000099 + 1 = 000100;

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

saivko SSC Eights! Points: 898 More actions · Answer 4

Thanks! Works beautiful.

Any advantages/disadvantages of using 'convert' vs. 'cast' ?

Maybe convert is slower but safer?

GSquared SSC Guru Points: 260824 More actions · Answer 5

saivko (8/7/2009)
Thanks! Works beautiful.
Any advantages/disadvantages of using 'convert' vs. 'cast' ?
Maybe convert is slower but safer?

No performance advantage.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 6

GSquared (8/7/2009)
saivko (8/7/2009)
Thanks! Works beautiful.
Any advantages/disadvantages of using 'convert' vs. 'cast' ?
Maybe convert is slower but safer?
No performance advantage.

Why not store the value as a number, and do the casting on its way out? Seems like a whole lot of effort into the wrong way to do things......

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

GSquared SSC Guru Points: 260824 More actions · Answer 7

Matt Miller (8/7/2009)
GSquared (8/7/2009)
saivko (8/7/2009)
Thanks! Works beautiful.
Any advantages/disadvantages of using 'convert' vs. 'cast' ?
Maybe convert is slower but safer?
No performance advantage.
Why not store the value as a number, and do the casting on its way out? Seems like a whole lot of effort into the wrong way to do things......

I agree with you on that. I just stated that Convert and Cast don't have performance advantages one vs the other.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 8

GSquared (8/7/2009)
I agree with you on that. I just stated that Convert and Cast don't have performance advantages one vs the other.

I know you know. I just hadn't seen anytone object to the course of behavior yet, so I figured I'd do it...:)

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

GSquared SSC Guru Points: 260824 More actions · Answer 9

Yeah, I could have challenged it more. Did that to someone who needed an ID with a "U" on the front of it, just the other day. Said add in the "U" in a computed column and just plain use an Int ID for the incrementing number.

Don't know what it was for, didn't feel like challenging it, this time.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 10

I'm not sure I satisfied the "elegant" requirement, more the "it works" requirement..

CEWII

Jeff Moden SSC Guru Points: 1004434 More actions · Answer 11

saivko (8/7/2009)
What is an elegant way to Select +1 to a nchar that will always have alpha characters? while persevering the zero's
The description above explains what I'm thinking:
000021 + 1 = 000022;
000099 + 1 = 000100;

This is absolutely the wrong thing to store in a database and you're suffering because of it. The correct thing to do is store it as an INTEGER and only display the leading zeros using the tools available in the GUI.

If you absolutely gotta have it available in the LPZ (Left Padded Zeros) format, then create a view or a calculated column to produce the LPZ format. But don't store this kind of formatted stuff in a database!! 😉

--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)

Jeff Moden SSC Guru Points: 1004434 More actions · Answer 12

p.s. Pork chops to the "designer" of the table that put you in this pickle to begin with. 😛

--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)

saivko SSC Eights! Points: 898 More actions · Answer 13

Thanks, yeah, an int would definitely be nicer. I inherited this setup. This text/number is even the record ID. The user who made a new record manually entered in one of their choosing! :pinch: I just set it to generate the next unused "ID". If I changed it to an int now, I would have a lot of other work to do since this ID gets used in a lot of tables. Not to mention asp.net code that reads it

yeah, porkchops to the designer alright, heh. They originally intended the number to be the EIN (employee identification number) and that was supposed to make things unique..... but then, as exceptions always happen, sometimes there was no EIN data available for some records, soon the later records just ignored the EIN part. They became any number with the same digits as an EIN.... preferably with leading zeros. (I don't think any real EIN has leading zeros).

Jeff Moden SSC Guru Points: 1004434 More actions · Answer 14

saivko (8/8/2009)
Thanks, yeah, an int would definitely be nicer. I inherited this setup. This text/number is even the record ID. The user who made a new record manually entered in one of their choosing! :pinch: I just set it to generate the next unused "ID". If I changed it to an int now, I would have a lot of other work to do since this ID gets used in a lot of tables. Not to mention asp.net code that reads it
yeah, porkchops to the designer alright, heh. They originally intended the number to be the EIN (employee identification number) and that was supposed to make things unique..... but then, as exceptions always happen, sometimes there was no EIN data available for some records, soon the later records just ignored the EIN part. They became any number with the same digits as an EIN.... preferably with leading zeros. (I don't think any real EIN has leading zeros).

Heh... understood on the inheritance of garbage` (French for garbage :-P). One of the problems with calculating a "next ID" in such a fashion is deadlocks and, possibly, two folks getting the same ID... you have to make a transaction to ensure that doesn't happen and that transaction can also lead to deadlocks unless you're very, very careful, use a "sequence" table, and do the update to the sequence table just right using the 3 part update available in SQL Server. We can help you do that if you think that might be something you want to do.

Better yet, if you're a bit daring and understand that you should test it very well before promoting it to production, you can add an autonumbering column for the EID, create a calculated column with the same name as the original EID column that adds the leading zero's, and basically be done with it all without any chance of it causing a deadlock or duplicate EID.

--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)