Replace all white space with comma and new line - RegEx

[Arthur]

For those who need to quickly replace any white space chars with one or a series of another perhaps especially who's extracting data from databases or need to make a quick SELECT statement often where table fields are returned from a definition script or a query in a space delimited format can use the following RegEx to add a comma with a new line to break the table fields into an almost ready to use 'select' statement:

Ctrl-H brings up the Replace dialog in which the following should be chosen: "Use Regular Expression", and those two

Search Pattern (Find What): \s+
Replace With: ,\n

Toggle the 'Entire Document' option on, and if for example, in my case I had the table fields as Col1 Col2 Col3 I get
Col1,
Col2,
Col3
which I can quickly wrap into a SELECT statement.

[jussij]

Hi Arthur,

Another more automated option is to wrap your regep inside a Zeus macro.

As the description in the macro states, it will take a line of column names and create the corresponding select statement.

Cheers Jussi

Code: Select all

--
--        Name: Create Select Macro
--
--      Author: Jussi Jumppanen
--
--    Language: Lua Macro
--
-- Description: This function takes a line of column names and creates
--              a suitable select statement.
--
--              To use the macro place the cursor on a line containing
--              the column names.

function trim(s)
    return (s:gsub("^%s*(.-)%s*$", "%1"))
end

function key_macro()
    -- macro only works for documents
    local document = is_document()

    -- macro only works for read/write documents.
    local locked = is_read_only()

    if (locked == 1) or (document == 0) then
        message("This macro only works for writable document files.");
        beep();
        return;
    end

    screen_update_disable()

    -- default to the current line
    local top   = get_line_pos()
    local range = 1

    -- save the current cursor
    cursor_save()

    -- macro only works for marked documents
    local marked = is_marked()

    if marked == 1 then
        -- remove the current markings
        MarkHide()
    end

    -- format the line of columns into multiple rows of columns
    local columns = trim(string.gsub(get_line_text(), "(%w+)([ \\t]*)", "%1,\n       "))

    -- build uo the slect statement (removing the erronous traling comma)
    local result  = "SELECT " .. string.sub(columns, 1, string.len(columns) - 1) .. "\n" .. "FROM "

    -- delete the line of columns
    LineDelete()

    MoveLineHome()

    -- add in the newly created select statement
    write(result, 0)

    -- restore original cursor and markings
    cursor_restore()

    screen_update_enable()
    screen_update()
end

key_macro() -- run the macro