Sequel3
Sequel3

Reputation: 75

Notepad ++ : Selecting and converting an entiere column into a string

I am trying to build a INSERT MS SQL query in Notepad++

I seek help from the community on how to carry out the following tasks:

E.g. of the data

2166    0   2020-09-21      JPP 172432  10.70   0   2021110 123403948123    938 0000002930BC3040303 SALE    1

Desired output

(2166,0,'2020-09-21','JPP',172432,10.70,0,2021110,123403948123,938,'0000002930BC3040303','SALE',1),

Upvotes: 1

Views: 994

Answers (3)

Toto
Toto

Reputation: 91428

Here is a way to go, you have to add manually parenthesis at the beginning and at the end:

  • Ctrl+H
  • Find what: \b(?:([\d.]+)|(\S+))\h+
  • Replace with: (?1$1)(?2'$2'),
  • CHECK Wrap around
  • CHECK Regular expression
  • Replace all

Explanation:

\b              # word boundary
(?:             # non capture group
    ([\d.]+)        # group 1, 1 or more digit or dot
  |               # OR
    (\S+)           # group 2, 1 or more non space
)               # end group
\h+             # 1 or more horizontal space

Replacement:

(?1             # if group 1 exists (i.e. only digits and dot)
    $1              # put group 1 without any changes
)               # endif
(?2             # if group 2 exists (i.e. string values)
    '$2'            # put group 2 surrounded with quotes
)               # endif
,               # comma

Screenshot (before):

enter image description here

Screenshot (after):

enter image description here

Upvotes: 1

logi-kal
logi-kal

Reputation: 7880

Select "Regular expression" as "Search Mode". Then:

  • Find: (?m)^((?:[^\t]*\t){N})([^\t]*)
  • Replace: $1'$2'

Where N is the field that you want to turn into a string (starting from 0).

Regex explanation:

  • (?m) is the multi-line flag.
  • ^ matches the beginning of the row (or of the string, if you are not in multi-line mode).
  • [^\t] matches a non-tab characther.
  • * is the zero-or-more operator.
  • (...) is a capturing group, i.e. a group that can be referenced in the replacement.
  • (?:...) is a non-capturing group.

In order to obtain your expected result, in a second moment you can replace all the \t with ,, all the beginning of rows (i.e. (?m)^) with ( and all the endings of rows (i.e. (?m)$) with ),.

Upvotes: 1

Robo Robok
Robo Robok

Reputation: 22703

Replace with regex:

^(.+?)\t(.+?)\t(.+?)\t(.+?)\t(.+?)\t(.+?)\t(.+?)\t(.+?)\t(.+?)\t(.+?)\t(.+?)\t(.+?)\t(.+?)$

To:

($1,$1,'$3','$4',$5,$6,$7,$8,$9,$10,'$11','$12',$13),

Upvotes: 1

Related Questions