How to convert string to a number?

Morning,

I need assistance with converting string to number. In my trailer row I have the record count.

I need the count field to be right justified, padded with zeros (8). ex.00000732

I originally had right(PadLeft(ToString([Count]), 8, "0"),8) which worked when i didn't need it separated by commas. Now I need my trailer row to be comma separated instead of one string. Thanks in advance

Before:ZZZQUESBIO2016021900000272

I need it to look like this ZZZ,QUES,BIO,20160219,00000272

Preparation

Accepted answers

jdunkerley79

I would look at the regex_replace function

Something like:

REGEX_Replace([Field1],'^(\w{3})(\w{4})(\w{3})(\d{8})(\w{8})$','$1,$2,$3,$4,$5')

Attached a demo.

I assumed the field length based on your example. Regex can hopefully be adjusted to match your needs.

RegexCommas.yxmd

All comments

jdunkerley79

I would look at the regex_replace function

Something like:

REGEX_Replace([Field1],'^(\w{3})(\w{4})(\w{3})(\d{8})(\w{8})$','$1,$2,$3,$4,$5')

Attached a demo.

I assumed the field length based on your example. Regex can hopefully be adjusted to match your needs.

RegexCommas.yxmd

Dom

Perfect!

Quick Links

This months top contributors

hhaigh1 280

FláviaB 262

JORGE4900 235

caltang 177

DanM 167