背景

找了很久沒找到比較好用的格式化工具庫。而 Navicat、DBeaver、IDEA 等工具對 SQL 結構影響較大。所以嘗試使用正則表達式替換 SQL 的關鍵字為大寫。

實現

保留字：SELECT keyword FROM v$reserved_words;

const { padEnd } = require('lodash')// 關鍵字 const KEYWORD_LIST = [...['CREATE', 'DROP', 'ALTER', 'COMMENT', 'TABLE', 'COLUMN', 'SEQUENCE'],...['VARCHAR2', 'NUMBER', 'DATE', 'NULL', 'SYSDATE', 'DEFAULT'],...['ROWID', 'ROWNUM'],...['WITH'],...['SELECT', 'DISTINCT', 'AS', 'CASE', 'WHEN', 'THEN', 'ELSE', 'END'],...['UPDATE', 'SET'],...['DELETE'],...['INSERT', 'INTO', 'VALUES'],...['FROM', 'JOIN', 'ON'],...['WHERE', 'AND', 'OR', 'NOT', 'IN', 'EXISTS', 'BETWEEN', 'IS'],...['GROUP', 'BY', 'PARTITION', 'OVER'],...['HAVING'],...['ORDER', 'ASC', 'DESC'],...['MERGE', 'USING', 'MATCHED'] ]// 函數 const FUNC_LIST = [...['MAX', 'MIN', 'COUNT', 'AVG'],...['DECODE', 'NVL', 'NVL2', 'REPLACE'],...['TO_DATE', 'TO_CHAR', 'TO_NUMBER'],...['ADD_MONTHS'],...['ROUND', 'RTRIM', 'LTRIM', 'REPLACE'],...['RANK', 'DENSE_RANK', 'ROW_NUMBER'] ]function split(str) {const words = []let i = 0for (let c of str.split('')) {const strIng = words[i] !== undefined && words[i].split('').filter(it => it === "'").length % 2 === 1const opt = /^(\s|\+|-|\*|\/|\(|\)|>|<|=|,|;)$/i.test(c)if (strIng || !opt) {words[i] = (words[i] || '') + c} else {words.push(c)i = words.length}}return words }function formatWord(word) {// 忽略空白字符if (/^\s*$/i.test(word)) return wordfor (let keyword of [...KEYWORD_LIST, ...FUNC_LIST]) {if (word.toUpperCase() === keyword) return keyword}return word.toLowerCase() }function formatLine(line) {// 忽略行注釋if (/^\s*--/i.test(line)) return line// 字段對齊if (/^\s*[a-zA-Z0-9_]+\s(NUMBER|VARCHAR2\([0-9]+\)|DATE)/i.test(line)) {const [col, ...arr] = line.trim().split(' ')line = ` ${padEnd(col, 30)} ${arr.join(' ')}`} else if (/^\s*COMMENT ON COLUMN [a-zA-Z0-9_]+(\.[a-zA-Z0-9_]+)? IS/i.test(line)) {const words = line.trim().split(' ')const [table, column] = words[3].split('.')words[3] = `${table}.${padEnd(column, 30)}`line = words.join(' ')}// 轉換大小寫const words = split(line)for (let i = 0; i < words.length; i++) {words[i] = formatWord(words[i])}line = words.join('')return line }function format(sql) {sql = sql.split('\n').map(line => formatLine(line)).join('\n')return sql }module.exports = {format } const fs = require('fs') const path = require('path') const { format} = require('./prettify')// 格式化 let sql = fs.readFileSync(path.join(__dirname, './source.sql'), 'UTF-8') sql = format(sql) fs.writeFileSync(path.join(__dirname, './target.sql'), sql)

總結

以上是生活随笔為你收集整理的使用正则表达式大写SQL关键字的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。