scheduled import job

[chercm]

i am using a walkup experience user badge table and a sql server with badge numbers and facility number . i will be using scheduled import job to import the user badge details to the user_badge table in servicenow . 

But the badge information from the sql data is having xxxx:xxxx , how can i extract the number after the colon during the import process to be place into the badge column in the user_badge table ?

[Medi C]

@chercm 
If you are using a SQL statement, could you please try the following for the affected field

SELECT FORMAT(YOURFIELD, '000000000') AS YOURFIELD_1

For the number of 0s, please adjust it per how many digits are there on your field. 

Example:

SELECT FORMAT(42, '00000') AS PaddedNumber;

Would return:

00042

This may create an additional field "YOURFIELD_1" in your import set table.

[Medi C]

@chercm 

You can also try

SELECT LPAD(YOURFIELD, 10, '0') AS YOURFIELD_1

Where 10 is the number of characters on the field on the DB size

[chercm]

@Medi C  What is the 0 in select lpad???

[Ankur Bawiskar]

@chercm 

did you try the approach I shared earlier?

sharing again

if you are sure which character is that, copy from that source field value and split using that

OR

simply if you are sure the colon will appear after 5 characters then use substring like this but please enhance

var str = '00235:04280';

var substringval = str.substring(6,11);

gs.info(substringval);

If my response helped please mark it correct and close the thread so that it benefits future readers.

[Medi C]

Hi @chercm,

I hope you are doing well! the 0 in the function represents the leading digits.

SELECT LPAD(YOURFIELD, 10, '0') AS YOURFIELD_1

Example: If your field would have 5 characters, the other 5 would be 0s to have 10 chars in total.