how to repeat characters in a string

Question

I'm trying to link two tables, one has an 'EntityRef' that's made of four alpha characters and a sequential number...

EntityRef
=========
SWIT1
LIVE32
KIRB48
MEHM38
BRAD192

The table that I'm trying to link to stores the reference in a 15 character field where the 4 alphas are at the start and the numbers are at the end but with zeros in between to make up the 15 characters...

EntityRef
=========
SWIT00000000001
LIVE00000000032

So, to get theses to link, my options are to either remove the zeros on one field or add the zeros on the other.

I've gone for the later as it seems to be a simpler approach and eliminates the risk of getting into problems if the numeric element contains a zero.

So, the alpha is always 4 characters at the beginning and the number is the remainder and 15 minus the LEN() of the EntityRef is the number of zeros that I need to insert...

left(entityref,4) as 'Alpha',
right(entityref,len(EntityRef)-4) as 'Numeric',
15-len(EntityRef) as 'No.of Zeros'


Alpha   Numeric No.of Zeros
=====   ======= ===========
SWIT    1       10
LIVE    32      9
KIRB    48      9
MEHM    38      9
MALL    36      9

So, I need to concatenate the three elements but I don't know how to create the string of zeros to the specified length...how do I do that??

Concat(Alpha, '0'*[No. of Zeros], Numeric)

What is the correct way to repeat a character a specified number of times?

Possible duplicate of [Pad a string with leading zeros so it's 3 characters long in SQL Server 2008](https://stackoverflow.com/questions/16760900/pad-a-string-with-leading-zeros-so-its-3-characters-long-in-sql-server-2008) — pix, May 29 '19 at 10:53

score 1 · Answer 1 · answered May 29 '19 at 10:54

1

You can use string manipulation. In this case:

LEFT() to get the alpha portion.
REPLICATE() to get the zeros.
STUFF() to get the number.

The query:

select left(val, 4) + replicate('0', 15 - len(val)) + stuff(val, 1, 4, '')
from (values ('SWIT1'), ('ABC12345')) v(val)

answered May 29 '19 at 10:54

Gordon Linoff

1,242,037
58
646
786

Pretty much what I've come up with although I've not used Stuff() before - so I'll check it out - thanks! – dazzathedrummer May 29 '19 at 10:59

Tim Biegeleisen · Answer 2 · 2019-05-29T11:03:08.487

1

You may try left padding with zeroes:

SELECT
    LEFT(EntityRef, 4) +
    RIGHT('00000000000' + SUBSTRING(ISNULL(EntityRef,''), 5, 30), 11) AS EntityRef
FROM yourTable;

Demo

edited May 29 '19 at 11:03

answered May 29 '19 at 10:56

Tim Biegeleisen

502,043
27
286
360

It's dynamically specifying the number of zeros that's the problem - I think I've found the answer with Replicate('0', 15-Len(EntityRef) – dazzathedrummer May 29 '19 at 10:58
@dazzathedrummer My answer works. Did you [check the demo](https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=42fca8b9bf2233ed4c1fd6fa414b0c12) ? – Tim Biegeleisen May 29 '19 at 11:03
You are right, sorry, I saw the zeros after the RIGHT( and assumed it would populate a fixed number of zeros - this is a good answer. – dazzathedrummer May 29 '19 at 11:39

score 0 · Answer 3 · answered May 29 '19 at 11:02

0

With casting to integer the numeric part:

select * 
from t1 inner join t2
on concat(left(t2.EntityRef, 4), cast(right(t2.EntityRef, 11) as bigint)) = t1.EntityRef

See the demo.

answered May 29 '19 at 11:02

forpas

160,666
10
38
76

score 0 · Answer 4 · answered May 29 '19 at 11:02

0

I found the answer as soon as I posted the question (sometimes it helps you think it through!).

(left(entityref,4) + replicate('0',15-len(EntityRef)) + 
right(entityref,len(EntityRef)-4)),

answered May 29 '19 at 11:02

dazzathedrummer

511
2
10
26

how to repeat characters in a string

4 Answers4

Demo