How to make multiple lines in one line in notepad++| How to convert multiple lines SQL query into single line using notepad++

Currently  am working on Snowflake stored procedure in which upsert operation has to be performed and for that wrote a merge query which spans over multiple lines.

It is for better understanding and better readability but when I incorporated it in the stored procedure (Java script based)... it gave error.

JavaScript compilation error: Uncaught SyntaxError: Invalid or unexpected token in SPROC....


So it doesn't like multiline queries, thus I needed it to be in single line.

But there is no provision in snowflake worksheet to do it  which makes my stored procedure manageable.

It can be done manually, but is there a better way - quick and less error prone.

Yes, with the help of notepad++ 

Let's take for example below multiline query-- 

merge into t1 using t2 on t1.t1key = t2.t2key
    when matched and t2.marked = 1 then delete
    when matched and t2.isnewstatus = 1 then update set val = t2.newval, status = t2.newstatus
    when matched then update set val = t2.newval
    when not matched then insert (val, status) values (t2.newval, t2.newstatus);



When you are writing query it's good to have multiline as it's more readable, so keep it that way. 

Once you are done with query and satisfied then you can add the merge query in the SProc (highlighted in yellow)

snowflake stored procedure example


Before adding, make it in single line , and to do that we take help of Notepad++  and there are two ways to convert multiple lines into single line

1. Select the query --> go to Edit menu --> Line Operations --> Join Lines

using join lines in notepad++


2. Select the query
i.   Open the Replace dialog ( Ctrl + H )
ii.  Check the Wrap around option
iii. Choose the Regular expression search mode
iv. Fill in the regex (\h*\R)+ in the Find what section
v.  Fill in the regex \x20 in the Replace with section
vi. Click on the Replace All button

multiline to single line using replace method

And result of both method, is same - query in single line

single line query


Now add this to the stored procedure

Snowflake stored procedure with single line query


If you know some other way, please feel free to share it in comments.

Post a Comment

If you have any suggestions or questions or want to share something then please drop a comment

Previous Post Next Post