cPanel Question posted in
The official documentation can be seen here.

Convert characters in string to numeric fixed positions ,2|4|6| needs to be ,020406 – CPanel

I have many files that need to be altered in order to import into cpanel, mysql. The number pattern is layout out like ,2|5|7|31|37|43|5
To import the file into the correct field, the data needs to be in format of 02050731374305

Is it better to just use notepad++ and edit all the files or is there a better way to import this data? Mysql field only allows numeric and is limited to 14 digits on this particular file.

The files I have are pretty much alike but vary in length. There are only 2 columns total in the file to import into mysql, cpanel.

reg expression might work but I have been finding errors on the replacement as it is picking up other numbers in the row and changing the data.

Tags:

3

Answers


  1. 0

    Here’s a quick solution using awk:

    echo "2|5|7|31|37|43|5" | awk -F'|' '{printf("%02d%02d%02d%02d%02d%02d%02dn", $1, $2, $3, $4, $5, $6, $7)}'
02050731374305
    Login or Signup to reply.
  2. 0

    as I a said a fucntion, that breaks the string and build the new one, will help you

    DELIMITER //
CREATE DEFINER=`root`@`localhost` FUNCTION `new_function`(fullstr TEXT, delim char(1)) RETURNS char(16) CHARSET utf8mb4
    DETERMINISTIC
BEGIN
DECLARE inipos INTEGER;
DECLARE endpos INTEGER;
DECLARE maxlen INTEGER;
DECLARE item VARCHAR(100);

SET inipos = 1;
SET @temptxt = '';
SET fullstr = CONCAT(fullstr,delim);
SET maxlen = LENGTH(fullstr) ;

REPEAT
    SET endpos = LOCATE(delim, fullstr, inipos);
    SET item =  SUBSTR(fullstr, inipos, endpos - inipos);

    IF item <> '' AND item IS NOT NULL THEN           
        SET @temptxt = CONCAT(@temptxt,LPAD(item,2,'0'));
    END IF;
    SET inipos = endpos + 1;
UNTIL inipos >= maxlen END REPEAT;
RETURN @temptxt;
END/7
DELIMITER ;

    So you can

    SELECT new_function('2|5|7|31|37|43|5','|')

    And get

    02050731374305
    Login or Signup to reply.
  3. 0

    This can easily be done with Notepad++, using the power of lookaround.

    This will add a zero before each digit alone and it works for any number of values:

    • Ctrl+H
    • Find what: (?<!d)(?=d(?!d))
    • Replace with: 0
    • CHECK Wrap around
    • CHECK Regular expression
    • Replace all

    Explanation:

    (?<!            # negative lookbehind, make sure we haven't before:
  d              # a digit
)               # end lookbehind
(?=             # positive lookahead, make sure we have after:
  d              # digit
  (?!d)          # not followed by another digit
)               # end lookahead

    Screenshot (before):

    enter image description here

    Screenshot (after):

    enter image description here

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search