Reputation: 75
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:
'2020-9-21'
(
and ),
at the start and end of each lineE.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
Reputation: 91428
Here is a way to go, you have to add manually parenthesis at the beginning and at the end:
\b(?:([\d.]+)|(\S+))\h+
(?1$1)(?2'$2'),
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):
Screenshot (after):
Upvotes: 1
Reputation: 7880
Select "Regular expression" as "Search Mode". Then:
(?m)^((?:[^\t]*\t){N})([^\t]*)
$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
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