Add 0's between string

Transact-SQL
1

i need to add 0's between string
ex: if string is abc123455
then it should return as abc000123455

if string is abd3454565
then it should return as abd003454565
therefore the length should be 12.

String always have first 3 as character followed by numbers

please help me with a solution

2 
with cteSource AS
( SELECT 'abc123455' as col1 UNION ALL
  SELECT 'abd3454565'
)

SELECT
    LEFT(col1,3) + 
      REPLACE(STR(SUBSTRING(col1,4,LEN(col1)-3),12-3),' ','0') as newCol1
    ,LEFT(col1,3) +
      RIGHT('000000000' + SUBSTRING(col1,4,LEN(col1)-3),12-3) as newCol2
FROM
  cteSource AS S

output:

newCol1 newCol2
abc000123455 abc000123455
abd003454565 abd003454565

dbfiddle here

1 Like
3

Thank you stepson it works good :slight_smile:

i need one more help

in same concept if the string ending with alphabet should be 13 digits - Include sufficient 0s betweek first three alphabets & numbers .

please help me with this

4

Show an exemple of it

5

hi i have solved it like this

Create Data 
drop table #data
go 

create table #data
(
string123 varchar(100)
)
go 

insert into #data select 'abc11122'
insert into #data select 'dqw1234455'
insert into #data select 'fgt775'
go 

select * from #data 
go
SQL 
SELECT  *Substring* (string123, 1, 3) 
+  *Replace* ( *Space* (10 -  *Len* ( *Substring* (string123, 4,  *Len* (string123)))) + 
*Substring* ( 
string123, 4,  *Len* (string123)), ' ', 0) AS LeftPad 
FROM #data 

go
Result

image

1 Like
6

ex:
if string is abc1234g
then output should be abc000001234g

if string is asc12345h
then output should be asc000012345h

therefore the len(string) should be 13

7

Thank you Harishgg1

8

in script replace 12 with 13

9 
with cteSource AS
( SELECT 'abc123455' as col1 UNION ALL
  SELECT 'abd3454565' union all
  SELECT 'abc1234g' UNION ALL
  SELECT 'asc12345h'
)
,cteSourcePlus AS
(SELECT col1
   , CASE WHEN RIGHT(col1,1) LIKE '[a-zA-Z]' THEN 1 ELSE 0 END AS isAlpha
   , CASE WHEN RIGHT(col1,1) LIKE '[a-zA-Z]' THEN RIGHT(col1,1) ELSE '' END AS lastAlpha
FROM cteSource)

SELECT
    
  LEFT(col1,3) 
       + RIGHT('000000000' 
               + SUBSTRING(col1,4,LEN(col1) - (3+isAlpha)) 
                ,12-3) /* for numeric part*/
       + lastAlpha /* for alphabetic part*/
   AS newCol2
FROM
  cteSourcePlus AS S

output:

newCol2
abc000123455
abd003454565
abc000001234g
asc000012345h

dbfiddle here

10

Thank you so much stepson :slight_smile: this works

select case
when RIGHT(col1,1) LIKE '[0-9]' then LEFT(invoice,3) +RIGHT('000000000000' + SUBSTRING(col1,4,LEN(col1)-3),12-3)/for Numeric/
when RIGHT(col1,1) LIKE '[a-zA-Z]' THEN LEFT(invoice,3) +RIGHT('000000000000' + SUBSTRING(col1,4,LEN(col1)-3),13-3)/for alpha/
end string from table1

11

Super! Glade that it was solved!

12

Hi moh_sul,

Here you can see it:

---------------------------
--table definition

CREATE TABLE dbo.codes(
 id INT IDENTITY(1, 1) PRIMARY KEY,
 code1 VARCHAR(12),
 code2 VARCHAR(12)
 );

---------------------------
--insert samples

INSERT INTO dbo.codes
 VALUES
     ('abc123455', NULL),
     ('abd3454565', NULL),
     ('abc3456', NULL),
     ('abc63462', NULL);


---------------------------
--update the table

UPDATE dbo.codes
SET code2 = LEFT(code1, 3) + RIGHT(REPLICATE('0', 12) + SUBSTRING(code1, 4, 12), 9);

select * from dbo.codes;

Regards,
gigawatt38
MCSA: SQL 2016 Database Development
MCSE: Data Management and Analytics

Tested by: http://rextester.com/l/sql_server_online_compiler

13

;WITH cteSource AS
( SELECT 'abc123455' as col1 UNION ALL
SELECT 'abd3454565' union all
SELECT 'abc1234g' UNION ALL
SELECT 'asc12345h'
)

SELECT
    col1,
    STUFF(col1, 4, 0, REPLICATE('0', 12 - LEN(col1) +
        CASE WHEN RIGHT(col1,1) LIKE '[a-zA-Z]' THEN 1 ELSE 0 END))
   AS newCol2
FROM
  cteSource AS S
2 Likes
14

I was going to post this solution - but you beat me to it...:wink:

15

Drop table Characters
create table Characters
(
Titles char(50)
)

insert into Characters values('abc123')
insert into Characters values('abc35567')
insert into Characters values('rrr8911789')
insert into Characters values('abc12')

select b + a from (
select left(Titles,(patindex('%[0-9]%',Titles)-1)) as b,right(rtrim(('00000000' + substring(Titles,patindex('%[0-9]%',Titles),len(Titles)))),9) as a

from Characters
)c