HOW TO PUT COMMA or ANY OTHER CHARACTER IN MULTI LINE VALUE WHILE WRITING SQL QUERY IN SSMS(SQL SERVER MANAGEMENT STUDIO)
There are situations when multiple COMMA separated values need to be passed to IN keyword in SQL Select or Update or Delete command(query or statement).
For an example if DBA has got certain list of Employee from HR to be updated with Terminated Flag equals to 'Yes' in Employee table.
Here is the list of Employees to be updated:
DBA can copy the list and write the below query to actually confirm whether list contains all the "Active" Employee:
SELECT * FROM HR.Employees WHERE Empid IN (
4
56
10
34
67
79
12
656
565
55
)
The above query will certainly throw en error as Employee IDs are not comma delimited. In order to solve this issue we need to follow below steps:
That was the one mechanism , here are the steps to do it another way:
There may be many other solutions like writing the code in Notepad or Notepad++ or creating macro or Concatenate values in Excel though above two are quite handy and viable as you don't have to use external application.
There are situations when multiple COMMA separated values need to be passed to IN keyword in SQL Select or Update or Delete command(query or statement).
For an example if DBA has got certain list of Employee from HR to be updated with Terminated Flag equals to 'Yes' in Employee table.
Here is the list of Employees to be updated:
| EmpID |
| 4 |
| 56 |
| 10 |
| 34 |
| 67 |
| 79 |
| 12 |
| 656 |
| 565 |
| 55 |
DBA can copy the list and write the below query to actually confirm whether list contains all the "Active" Employee:
SELECT * FROM HR.Employees WHERE Empid IN (
4
56
10
34
67
79
12
656
565
55
)
The above query will certainly throw en error as Employee IDs are not comma delimited. In order to solve this issue we need to follow below steps:
- Put cursor in the beginning of 56.
- Press ALT+SHIFT Key.
- Press the Down Arrow button on Keyboard.
- Release the Down Arrow once reach till second last value i.e. 55.
- Press the COMMA(,) button from Keyboard.
- You are done and now you are ready to execute your query.
SELECT * FROM HR.Employees WHERE empid IN (
4
,56
,10
,34
,67
,79
,12
,656
,565
,55
)
Now DBA can run the Update Query to manipulate listed EmplD with Terminated Flag='Yes'
UPDATE HR.Employee Set Terminated='Yes' WHERE EmpID IN (
4
,56
,10
,34
,67
,79
,12
,656
,565
,55
)
That was the one mechanism , here are the steps to do it another way:
- In the SSMS Query window, press CTRL+H.
- Find and Replace window is opened.
- Populate '\n' in Find What edit box.
- Populate COMMA(,) in Replace with edit box.
- Click on Replace All button.
- You are done and now you are ready to execute your query.
 |
| Find and Replace Line Feed (\n) with Comma(,) |
There may be many other solutions like writing the code in Notepad or Notepad++ or creating macro or Concatenate values in Excel though above two are quite handy and viable as you don't have to use external application.
