locked
[SSMS] How to add character to end of line? RRS feed

  • Question

  • I have a list of numeric values (copied from an Excel column) that I need to include in the IN clause, but before I do, I need to convert them to strings and separate them by commas. So I need to wrap each value in the list with ', . So given the list:

    123
    456
    789

    I need to end up with:

    '123',
    '456',
    '789',

    Keep in mind that the list may contain thousands of entries, so vertical select is not feasible.

    I can add the beginning single quote via search and replace by specifying the regular expression looking for the beginning for the line (^) and replacing it with the single quote character ('). But I cannot figure out how to add ', at the end. It used to work in the past using regular expression and end of line ($), but now when I specify the search string as $ and the replacement string as ', I get the new line appended before the quote character, so I end up with:

    '123
    ',
    '456
    ',
    '789
    ',

    Any idea how I can do it? I am using SSMS 18.5. 



    • Edited by Alek Davis Monday, July 6, 2020 6:45 PM
    Monday, July 6, 2020 6:45 PM

Answers

  • For SSMS - try:

    Find: ((\r?\n)|$)
    Replace: ',$0
    



    Jeff Williams

    • Marked as answer by Alek Davis Wednesday, July 8, 2020 11:24 PM
    Monday, July 6, 2020 10:06 PM
  • Find what:
    \r\n
    
    Replace with:
    ',\r\n'
    
    
    This works fine for me in SSM 18.4

    Please mark as answer, if this was it. Visit my SQL Server Compact blog http://erikej.blogspot.com

    • Proposed as answer by ErikEJMVP Tuesday, July 7, 2020 6:28 AM
    • Marked as answer by Alek Davis Wednesday, July 8, 2020 11:23 PM
    Tuesday, July 7, 2020 6:27 AM

All replies

  • When I have a problem like this I usually copy the numbers into Visual Code new file editor. The Visual Code allows to select the beginning of the line and type character and it automatically will be repeated till the end (as the first character) and then do the same for the end of the line (e.g. it allows to have the highlight to go directly to the end and select it like a line).

    Visual Code is free, you can download this editor and it's really helpful for lots of tasks, not just this kind. Say, it allows to design website using Angular and it has built in terminal.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles



    • Edited by Naomi N Monday, July 6, 2020 7:49 PM
    Monday, July 6, 2020 7:47 PM
  • You can prep the data in Excel - instead of trying to do that in SSMS.  In a new column in Excel - use the function:

    =", '" & <column reference> & "'"

    Copy the function to all rows in the Excel spreadsheet and Excel will update the function with the correct column reference.

    Now - just copy/paste the results into SSMS.  Remove the leading column on the first entry in the list.

    SELECT ...
    FROM ...
    WHERE somecolumn IN (
    ...< paste the results here > ...
    )


    Jeff Williams

    Monday, July 6, 2020 8:26 PM
  • I know how to do this in other editors (VSC, NP++, etc), but I am wondering what I am doing wrong in SSMS because it used to work as expected and now it does not. Is this a bug? Will it be fixed?

    A problem with other editors, is that I may need to do it on a hardened server with no apps other than SSMS installed and I do not control the data returned from the excel (I get data from the customers, so I'd need to ask them to do the Excel dance when it should be trivial to do in SSMS).

    Monday, July 6, 2020 8:35 PM
  • Another alternative:

    declare @str varchar(max)=
    '123
    456
    789'
    
    select '''' + replace(@str, char(13)+char(10), ''',' + char(13)+ char(10) + '''' ) + '''';
    Make sure to use Query - Output to text in order to see it properly.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles


    • Edited by Naomi N Monday, July 6, 2020 9:21 PM
    Monday, July 6, 2020 8:55 PM
  • I know how to do this in other editors (VSC, NP++, etc), but I am wondering what I am doing wrong in SSMS because it used to work as expected and now it does not. Is this a bug? Will it be fixed?

    I always do this kind of stuff in EditPlus. SSMS/Visual Studio seems to have its own idea of regular expressions. EditPlus uses the same regexp rules as Perl, which makes it very easy for me.
    The behaviour you discuss is certainly completely brain-dead. I am quite certain that it will not be fixed in SSMS 18, as this is something which comes with the Visual Studio 2017 shell, and when I try this in VS 2017 I get the same.

    I don't have VS 2019 installed, so I don't know if they have addressed it in VS 2019.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Monday, July 6, 2020 9:44 PM
  • For SSMS - try:

    Find: ((\r?\n)|$)
    Replace: ',$0
    



    Jeff Williams

    • Marked as answer by Alek Davis Wednesday, July 8, 2020 11:24 PM
    Monday, July 6, 2020 10:06 PM
  • Find what:
    \r\n
    
    Replace with:
    ',\r\n'
    
    
    This works fine for me in SSM 18.4

    Please mark as answer, if this was it. Visit my SQL Server Compact blog http://erikej.blogspot.com

    • Proposed as answer by ErikEJMVP Tuesday, July 7, 2020 6:28 AM
    • Marked as answer by Alek Davis Wednesday, July 8, 2020 11:23 PM
    Tuesday, July 7, 2020 6:27 AM
  • Keep in mind that the list may contain thousands of entries
    And keep in mind, such queries can fail, see IN Operator => Remarks.

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Tuesday, July 7, 2020 6:56 AM
  • Do you try Jeff Williams 3188's and ErikEJ's reply?

    Is the issue solved?

     
    Wednesday, July 8, 2020 5:41 AM
  • Find what:
    \r\n
    
    Replace with:
    ',\r\n'
    
    This works fine for me in SSM 18.4

    Please mark as answer, if this was it. Visit my SQL Server Compact blog http://erikej.blogspot.com

    Sweet. This seems to work, except you do not need the ending quote (for my particular case). Thank you.
    • Edited by Alek Davis Monday, July 13, 2020 6:19 PM
    Wednesday, July 8, 2020 11:23 PM
  • Find what:
    \r\n
    
    Replace with:
    ',\r\n'
    
    This works fine for me in SSM 18.4

    Please mark as answer, if this was it. Visit my SQL Server Compact blog http://erikej.blogspot.com

    No need for the ending quote in the Replace with string, so it should be:

    ',\r\n

    Monday, July 13, 2020 6:16 PM