Replacing part of a character datatype
-
I have a column that is defined as char(19). I need to keep the first 6 and last 4 characters and replace what is in between with zeros (i.e. 387643098846789 s/b 387643000006789). In most instances the field is never completely full (i.e. 14, 15 or 16 characters). This is how I am building the new column but I cannot seem to get the syntax correct:
SELECT sto_pos_bus_dt_id,
pos_trns_rcv_id,
pos_tn_stg_id,
rfd_tnr_ct,
acct_id,
Left(acct_id,6) & (Len(RTrim(acct_id))-10,'0') & (Right(RTrim(acct_id),4)AS NewAcct2
FROM aud_drt_rfdtnr_hst
Any help is appreciated!
-
Use the '+' instead of the '&' as the concatenation operator. That should get you pretty close.
And then again, I might be wrong ...
David Webb -
I changed the '&' to '+' and I'm still not having any luck. This is what I have now:
new_acct = Left(acct_id,6) + Len((acct_id)-10),'0' + Right(RTrim(acct_id),4)
And the error I'm receiving is: "The conversion of the varchar value '387643098846789 ' overflowed an int column. Maximum integer value exceeded." I'm confused why it states that it's converting a varchar value when the datatype is char.
The net result I am trying to get at is to keep the first 6 digits of the number and the last 4 digits of the number and replace the digits in between those with zeroes (however many there may be).
-
Check the parens on the length subtraction. I think it's trying to subtract 10 from the acct_id, not the length). Maybe should be:
(Len(acct_id)-10)),
And then again, I might be wrong ...
David Webb -
Either of these should do it.
select stuff(rtrim(acct_id),7,len(acct_id)-10,replicate('0',len(acct_id)-10)) from ( select acct_id =convert(char(19),'387643098846789 ') ) Aselect left(acct_id,6)+replicate('0',len(acct_id)-10)+right(rtrim(acct_id),4) from ( select acct_id =convert(char(19),'387643098846789 ') ) A
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply