Add semicolon every 7th place

[captainpicart]

Hi experts,

How to write a stored procedure which will get rid of all semicolons at the beginning and at the end, BUT will separate each number with a semi colon?


Input:---
018752;018752;019912;002541;017181;;;;
;018752;018752;018752;019536;020921;007378;013099;;;;;;;;
;;;018752;018752;018752;029437;017554;027857;031346;;;;;;;;;;;;;;;;;;;;;;;;;;;
;;018752;018752;018752;029437;017554;027732;011219;;;
;018752;018752;018752;019536;020921;007873;
;018752;018752;018752;019536;020921;007873

Output:-
018752;018752;019912;002541;017181
018752;018752;018752;019536;020921;007378;013099
018752;018752;018752;029437;017554;027857;031346
018752;018752;018752;029437;017554;027732;011219
018752;018752;018752;019536;020921;007873
018752;018752;018752;019536;020921;007873

Note: The numbers will always be 6 digits in length.

Please Help!

Thank you!

 

[gmmastros]

There's a neat little trick you can use.

1. Replace Semi-colons with spaces
2. Left Trim spaces
3. Right Trim spaces
4. Replace spaces with semi-colons.

Ex:

Declare @Temp Table(Data VarChar(100))

Insert Into @Temp Values('018752;018752;019912;002541;017181;;;;')
Insert Into @Temp Values(';018752;018752;018752;019536;020921;007378;013099;;;;;;;;')
Insert Into @Temp Values(';;;018752;018752;018752;029437;017554;027857;031346;;;;;;;;;;;;;;;;;;;;;;;;;;;')
Insert Into @Temp Values(';;018752;018752;018752;029437;017554;027732;011219;;;')
Insert Into @Temp Values(';018752;018752;018752;019536;020921;007873;')
Insert Into @Temp Values(';018752;018752;018752;019536;020921;007873')

Select Data, Replace(LTrim(RTrim(Replace(Data, ';', ' '))), ' ',';')
From   @Temp

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[captainpicart]

Awesome. Perfect!

Thanks gmmastros!

 

[gmmastros]

You're welcome.


-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom