Feature request: Copy as where clause

Answered

lets say you run a query which returns a column:

col1

100

200

200

300

it would be cool if you could highlight the values you want and 'copy as where clause'. when you then paste from the clipboard, you would get 'col1 in (100,200,300)'. note the deduplication

thanks!

4 comments
Comment actions Permalink

Hi,

I've created an issue based on your description https://youtrack.jetbrains.com/issue/DBE-4104

Thank you.

0
Comment actions Permalink

Hi, I was having the same need. After googling a little bit and based on one of the DataGrip extractors. I have come up with this data extractor.
Just create your own going to the data extractors folder. In DataGrip (Windows): Alt + 2, then go to Scratches and Consoles/Extensions/Database Tools And SQL/data/extractors. Create a new one let's say Custom.sql.groovy

SEP = ", "
QUOTE = "\'"
NEWLINE = System.getProperty("line.separator")
ROW_SEP = ","+NEWLINE

KEYWORDS_LOWERCASE = com.intellij.database.util.DbSqlUtil.areKeywordsLowerCase(PROJECT)
KW_NULL = KEYWORDS_LOWERCASE ? "null" : "NULL"

StringBuilder sb = new StringBuilder()
sb.append("(")
def record(columns, dataRow,sb) {
columns.eachWithIndex { column, idx ->
def value = dataRow.value(column)
def skipQuote = value.toString().isNumber() || value == null
def stringValue = value != null ? FORMATTER.format(dataRow, column).trim() : KW_NULL
if (DIALECT.getFamilyId().isMysql()) stringValue = stringValue.replace("\\", "\\\\")
sb.append(skipQuote ? "": QUOTE).append(stringValue.replace(QUOTE, QUOTE + QUOTE))
.append(skipQuote ? "": QUOTE).append(idx != columns.size() - 1 ? SEP : "")
sb.append(ROW_SEP)
}
}
ROWS.each { row -> record(COLUMNS, row,sb) }
sb = new StringBuilder(sb.substring(0, sb.length()-(ROW_SEP.length()))+")")
OUT.append(sb.toString())

Then close it. Make your query, in the upper right corner of the result window select your data extractor. Select the rows, and copy (ctrl + c [on Windows], or rigth click copy)
If you want to have the column name, just modify the script. If you want to copy multiple columns and get multiple SQL IN, just switch those for's that iterate over columns and rows.


Note: Because I needed, I trimmed the value of stringValue. If you don't need it just remove it.

 

I hope it helps and they keep this awesome feature. Maybe they should add this as one of the data extractors that come with the IDE by default.
https://intellij-support.jetbrains.com/hc/en-us/community/posts/205993979-What-objects-functions-are-available-for-custom-data-extractors-

Have fun!

0
Comment actions Permalink

Use a CSV Format. Just remove the trailing comma after paste

1
Comment actions Permalink

What is the status of this feature? I also do not like deleting the comma at the end every time

0

Please sign in to leave a comment.