Filling a char value with leadin zeros

Question

Post reply

Filling a char value with leadin zeros

Ian-232454

SSC Journeyman

Points: 92
More actions
October 26, 2005 at 8:48 am

#61040

I am looking for a way to fill a char(12) value with leading zeros.
I have a control table that controls the next customer number. I did not create the table or application so I am stuck working with this. Here is the issue
The table has a number stored as as char(12) which is the next available customer number. I need to get this number increment it by one and then store that numbr - the issue is that the number has leading zeros - data exapmle
--get customer number and increment control table by one
--leadin zeros must stay in place
declare @cus_no char(12) SET @cus_no = (select next_cus_no from ARCTLFIL_SQL)
declare @next_cus_no char(12) set @next_cus_no = @cus_no + 1
PRINT @cus_no
PRINT @next_cus_no
PRINT LEN(@next_cus_no)
the vlues for that query are
000000005152
5153
4
I need the next_cus_no to be 000000005153 and not 5153.
Any suggestions on how to acheive this?
Thanks in Advance

Viewing 11 posts - 1 through 10 (of 10 total)

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

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

once you have the next value > >

Update table set col = right(12, replicate('0', 11) + convert(varchar(12), @NextID))

Ian-232454 SSC Journeyman Points: 92 More actions · Answer 2

thank you sRGR'us. Though your statement would have produced a value of 12 due to the RIGHT() funtion I was able to build off of that.

If any one is interested the final statement that produced the desired results it is

replicate('0', 12 - LEN(@next_cus_no)) + convert(char(12), @cus_no + 1)

12 - LEN(@next_cus_no) - is the length of the char field (12) minus the length of the next_cus_no giving me the proper amount of zeros to lead the value with.

Thanks Again

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 3

Ninja's_RGR'us

SSC Guru

Points: 294069

October 26, 2005 at 9:25 am

#600246

oops, right (blabla, 12) .

Stewart Joslyn SSCertifiable Points: 6131 More actions · Answer 4

I konw this works - I just tested it! - but I would have expected Char(12) to pad with leading spaces whereas it actually trims its return value, which is the behaviour that I expect from VarChar.

Any comments?

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 5

Char pads with TRAILING spaces. Also those spaces are removed here since with take only the first 12 characters of the string.

Stewart Joslyn SSCertifiable Points: 6131 More actions · Answer 6

Stewart Joslyn

SSCertifiable

Points: 6131

October 27, 2005 at 7:00 am

#600554

Aahh - of course. Thanks.

val_cc SSC Rookie Points: 37 More actions · Answer 7

I think RGR'us' suggestion should be:

DECLARE @cus_no char(12)

DECLARE_cus_no char(12)

SET @cus_no = (SELECT next_cus_no FROM ARCTLFIL_SQL)

SET @next_cus_no = RIGHT(REPLICATE('0', 12) + CAST(@cus_no + 1 AS VarChar(12)), 12)

regards,

Carl Federl One Orange Chip Points: 25384 More actions · Answer 8

From your original post, slightly re-organized.

With a number stored as a character column, the risk of introducing a fatal bug is close to 100%. Strongly recommend changing the datatype.

I noticed that the column is named next_cus_no but would question if it really is the "NEXT" or the "last assigned"

In a multi-user database, the approach of read-calculate-update will occasionally lead to duplicate customer numbers. Below are 2 scripts that you can use to reproduce generation of duplicate numbers that should be run in SQL Query Analyzer (QA) in two different windows to simulate different users..

Here is the initial set-up:

create table SequenceTest

( SequenceTestKey integer not null

, CustomerID_lastAssigned INTEGER NOT NULL

-- Constraints will insure that this table has only one row

, constraint SequenceTest_p primary key (SequenceTestKey)

, constraint SequenceTest_C_SequenceTestKey check (SequenceTestKey = 1 )

)

go

insert into SequenceTest

(SequenceTestKey , CustomerID_lastAssigned )

values (1, 998)

go

In one QA connection, enter but do not execute:

declare @CustomerID_lastAssigned INTEGER

select @CustomerID_lastAssigned = CustomerID_lastAssigned

from SequenceTest

-- Dely 10 seconds to simulate the process being suspended by SQL Server

WAITFOR DELAY ('00:00:10')

set @CustomerID_lastAssigned = @CustomerID_lastAssigned + 1

Update SequenceTest

SET CustomerID_lastAssigned = @CustomerID_lastAssigned

select @CustomerID_lastAssigned

, * from SequenceTest

go

In another QA connection, copy the above SQL and then remove the WAITFOR DELAY command.

Execute the first QA (with the WaitFor) and

then before the first QA completes, execute the second QA (without the WaitFor)

Voila ! The same number will be assigned twice.

To prevent duplicate assignments, first update the table (which will cause a lock) and then select the new value.

This must be done within a transaction in order to prevent any other process from updating the row.

declare @CustomerID INTEGER

BEGIN TRANSACTION

Update SequenceTest SET CustomerID_lastAssigned = CustomerID_lastAssigned

select @CustomerID = CustomerID_lastAssigned

from SequenceTest

-- Any algorithms go here

set @CustomerID = @CustomerID + 1

Update SequenceTest SET CustomerID_lastAssigned = CustomerID

COMMIT

SQL = Scarcely Qualifies as a Language

Ian-232454 SSC Journeyman Points: 92 More actions · Answer 9

Unfortunately I can not change the dthe datatype, the database is for an accounting, general ledger and inventory application(Macola) and changing the datatype will kill the application not to mention the hundreds of reports that access this table. I have looked at this approach and have found that it is but impossible. I have been tasked with creating an ecomm interface for this system.

What would be my best approach for locking the control file table while I do the update? My goal is to rollback the update on the control file if the insert of a new customer fails.

At the moment I have a basic sproc to handle the update of the control file and customr tables. The proc basically copies data from one data row as a template, though a few of the data fields are changed based on variables...

CREATE PROCEDURE ecomm_addNewCus(

@contact char(40), --First Name & Last Name

@cus_name char(40), -- Company Name

@addr_1 char(40),

@addr_2 char(40),

@city char(26),

@state char(4),

@zip char(10),

@country char(20),

@phone_no char(20),

@phone_ext char(4),

@email_addr char(40)

)

AS

--get customer number and increment control table by one

declare @cus_no char(12) SET @cus_no = (select next_cus_no from ARCTLFIL_SQL)

declare @next_cus_no char(12) set @next_cus_no = @cus_no + 1

set @next_cus_no = replicate('0', 12 - LEN(@next_cus_no)) + convert(char(12), (@cus_no + 1))

update ARCTLFIL_SQL set next_cus_no = @next_cus_no

-- check to see if a company was defined if not use the customer contact name

IF @cus_name = ''

set @cus_name = @contact

DECLARE @search_name char(40) SET @search_name = UPPER(@cus_name)

--set date variable, needs to be converted to an int

DECLARE@start_dt int SET @start_dt = CAST(CONVERT(varchar(20), GETDATE(), 112) as int)

DECLARE @slspsn_no char(3) SET @slspsn_no = '020'

DECLARE @cus_type_cd char(5) SET @cus_type_cd = 'EUD02'

--get the territory code

DECLARE @terr char(2) SET @terr = (select territory from Products.dbo.arstatetbl where code = @state)

--set the last statement age date

DECLARE @last_stm_age_dt int set @last_stm_age_dt = 0

--set the user_def_fld_2 value

DECLARE @user_def_fld_2 char(30)

IF @country = 'CANADA' set @user_def_fld_2 = 'FCA'

IF @country = 'UNITED STATES' set @user_def_fld_2 = null

--set the user_def_fld_3 value

DECLARE @user_def_fld_3 char(30)

IF @country = 'CANADA' set @user_def_fld_3 = '1'

IF @country = 'UNITED STATES' set @user_def_fld_3 = '0'

--set the note_2 value

DECLARE @note_2 char(30)

IF @country = 'CANADA' set @note_2 = 'CN'

IF @country = 'UNITED STATES' set @note_2 = 'US'

--set the note_2 value

DECLARE @note_4 char(30)

IF @country = 'CANADA' set @note_4 = 'Londonderry, NH USA'

IF @country = 'UNITED STATES' set @note_4 = null

--set the alternate shipping address to DEFAULT and then create a DEFAULT shipping address in the alt shipping table

DECLARE @cus_alt_adr_cd char(15) set @cus_alt_adr_cd = 'DEFAULT'

INSERT INTO ARCUSFIL_SQL (cus_no, cus_name, search_name, addr_1, addr_2, city, state, zip, country, contact, contact_2,

phone_no, phone_no_2, phone_ext, phone_ext_2, fax_no, start_dt, slspsn_no, cus_type_cd, bal_meth,

stm_freq, cr_lmt, cr_rating, hold_fg, cus_origin, collector, fin_chg_fg, filler_0003, terr, curr_cd,

par_cus_no, par_cus_fg, ship_via_cd, ups_zone, ar_terms_cd, dsc_pct, ytd_dsc_given, txbl_fg, tax_cd,

tax_cd_2, tax_cd_3, exempt_no, sls_ptd, sls_ytd, sls_last_yr, cost_ptd, cost_ytd, cost_last_yr,

balance, high_balance, last_sale_dt, last_sale_amt, inv_ytd, inv_last_yr, paid_inv_ytd, last_pay_dt,

last_pay_amt, avg_pay_ytd, avg_pay_last_yr, last_stm_age_dt, amt_age_prd_1, amt_age_prd_2,

amt_age_prd_3, amt_age_prd_4, allow_sb_item, allow_bo, allow_part_ship, print_dunn_fg, cmt_1, cmt_2,

vend_no, tax_sched, cr_card_1_desc, cr_card_1_acct, cr_card_1_exp_dt, cr_card_2_desc, cr_card_2_acct,

cr_card_2_exp_dt, user_def_fld_1, user_def_fld_2, user_def_fld_3, user_def_fld_4, user_def_fld_5,

dflt_inv_form, loc, note_1, note_2, note_3, note_4, note_5, user_dt, user_amount, amt_age_oe_term,

cus_alt_adr_cd, rfc_no, email_addr, filler_0002)

SELECT @cus_no, @cus_name, @search_name, @addr_1, @addr_2, @city, @state, @zip, @country, @contact, contact_2,

@phone_no, phone_no_2, @phone_ext, phone_ext_2, fax_no, @start_dt, @slspsn_no, @cus_type_cd, bal_meth,

stm_freq, cr_lmt, cr_rating, hold_fg, cus_origin, collector, fin_chg_fg, filler_0003, @terr, curr_cd,

par_cus_no, par_cus_fg, ship_via_cd, ups_zone, ar_terms_cd, dsc_pct, ytd_dsc_given, txbl_fg, tax_cd,

tax_cd_2, tax_cd_3, exempt_no, sls_ptd, sls_ytd, sls_last_yr, cost_ptd, cost_ytd, cost_last_yr,

balance, high_balance, last_sale_dt, last_sale_amt, inv_ytd, inv_last_yr, paid_inv_ytd, last_pay_dt,

last_pay_amt, avg_pay_ytd, avg_pay_last_yr, @last_stm_age_dt, amt_age_prd_1, amt_age_prd_2,

amt_age_prd_3, amt_age_prd_4, allow_sb_item, allow_bo, allow_part_ship, print_dunn_fg, cmt_1, cmt_2,

vend_no, tax_sched, cr_card_1_desc, cr_card_1_acct, cr_card_1_exp_dt, cr_card_2_desc, cr_card_2_acct,

cr_card_2_exp_dt, user_def_fld_1, @user_def_fld_2, @user_def_fld_3, user_def_fld_4, user_def_fld_5,

dflt_inv_form, loc, note_1, @note_2, note_3,@note_4, note_5, user_dt, user_amount, amt_age_oe_term,

@cus_alt_adr_cd, rfc_no, @email_addr, filler_0002

FROM ARCUSFIL_SQL

WHERE cus_no = '999999999999'

--insert the shipping address into the alternate shipping table with a name of DEFAULT

INSERT INTO ARALTADR_SQL (cus_no, cus_alt_adr_cd, cus_name, addr_1, addr_2, city, state, zip, country, tax_cd,

slspsn_no, contact, phone_no, phone_ext, ship_via_cd, loc, email_addr)

VALUES (@cus_no, @cus_alt_adr_cd, @cus_name, @addr_1, @addr_2, @city, @state, @zip, @country, 'NON', @slspsn_no,

@contact, @phone_no, @phone_ext, 'BW', 'ORB', @email_addr)

GO

Carl Federl One Orange Chip Points: 25384 More actions · Answer 10

Just made a few adjustments that are bracketed by "--CCF-START"

and "--CCF-END"

CREATE PROCEDURE ecomm_addNewCus(

@contact char(40), --First Name & Last Name

@cus_name char(40), -- Company Name

@addr_1 char(40),

@addr_2 char(40),

@city char(26),

@state char(4),

@zip char(10),

@country char(20),

@phone_no char(20),

@phone_ext char(4),

@email_addr char(40)

)

AS

-- CCF-START NEW

setnocount on

setxact_abort on

-- CCF-END NEW

/*

CCF-START Move this Code

--get customer number and increment control table by one

declare @cus_no char(12) SET @cus_no = (select next_cus_no from ARCTLFIL_SQL)

declare @next_cus_no char(12) set @next_cus_no = @cus_no + 1

set @next_cus_no = replicate('0', 12 - LEN(@next_cus_no)) + convert(char(12), (@cus_no + 1))

update ARCTLFIL_SQL set next_cus_no = @next_cus_no

CCF-END End move

*/

-- check to see if a company was defined if not use the customer contact name

IF @cus_name = ''

set @cus_name = @contact

DECLARE @search_name char(40) SET @search_name = UPPER(@cus_name)

--set date variable, needs to be converted to an int

DECLARE @start_dt int SET @start_dt = CAST(CONVERT(varchar(20), GETDATE(), 112) as int)

DECLARE @slspsn_no char(3) SET @slspsn_no = '020'

DECLARE @cus_type_cd char(5) SET @cus_type_cd = 'EUD02'

--get the territory code

DECLARE @terr char(2) SET @terr = (select territory from Products.dbo.arstatetbl where code = @state)

--set the last statement age date

DECLARE @last_stm_age_dt int set @last_stm_age_dt = 0

--set the user_def_fld_2 value

DECLARE @user_def_fld_2 char(30)

IF @country = 'CANADA' set @user_def_fld_2 = 'FCA'

IF @country = 'UNITED STATES' set @user_def_fld_2 = null

--set the user_def_fld_3 value

DECLARE @user_def_fld_3 char(30)

IF @country = 'CANADA' set @user_def_fld_3 = '1'

IF @country = 'UNITED STATES' set @user_def_fld_3 = '0'

--set the note_2 value

DECLARE @note_2 char(30)

IF @country = 'CANADA' set @note_2 = 'CN'

IF @country = 'UNITED STATES' set @note_2 = 'US'

--set the note_2 value

DECLARE @note_4 char(30)

IF @country = 'CANADA' set @note_4 = 'Londonderry, NH USA'

IF @country = 'UNITED STATES' set @note_4 = null

--set the alternate shipping address to DEFAULT and then create a DEFAULT shipping address in the alt shipping table

DECLARE @cus_alt_adr_cd char(15) set @cus_alt_adr_cd = 'DEFAULT'

-- CCF-START NEW

--if not in a transaction, then start a transaction

--This is performed as late as possibile.

declare @Transactioninteger

set @Transaction= @@trancount

IF @Transaction = 0 BEGIN TRANSACTION

--get customer number and increment control table by one

declare @cus_no char(12)

, @cus_NBR integer

-- Lock the table by performing a fake update and get the current value

update ARCTLFIL_SQL

-- this is a little known TransAct/SQL that allows an variable assignment and an update in one statement.

set @cus_no= next_cus_no = next_cus_no

-- increment customer number by 1

set@cus_NBR = cast ( @cus_no as integer) + 1

-- reformat integer to character with leading zeroes

set@cus_no = replicate('0', 12 - LEN ( CAST ( @cus_NBR as char(12)) ) ) + CAST ( @cus_NBR as char(12) )

update ARCTLFIL_SQL

set next_cus_no = @cus_no

-- CCF-END new