Hi,
I have a long list (2221) string variables that each needs a quotation mark either side to be able to search a database via the inlist/list command. What is the easiest away to apply this?, e..g. where in ('A123456', 'B789123')
thanks
With that number of strings it might be best to create a Temp table containing the strings and then use EXISTS.
Anyway, what you want to do rather depends on the editor you are using. With SSMS:
Thanks. I’m using SSMS. The strings have been selected based on the number of appointments attended. Once I select the string variable plus DOB, appointment number and physician I will join to another table with demographic details. The string variable is the identifier across each table. I’m using SSMS as the editor
scripting
select ' " ' + column_name +' " , ' from Table_Names
Table_Names
Sam
Pam
Kam
Becomes
"Sam",
"Pam",
"Kam",
You to remove the trailing comma using stuff
Why do you have 2k+ variables? Most unusual. How are you getting these many variables, where from?
I'm thinking that it's not 2k VARIABLES the OP has. I'm thinking in 2K lines of data that needs to be treated as individual strings.
It's pretty easy to do. Lets say we have a list like the following in an SSMS window (from a copy'n'paste from a spreadsheet, for example)...
O254
C787
R57
U930
X596
W762
K667
L704
O767
V91
In order to convert that to a list of values where the values (except the first) are encapsulated by single quotes and each line (except the first) is started with a comma...
- Select the data on screen including the start of the first blank line after the data.
- Press {ctrl-H} to start the search'n'replace popup, turn on the "regex" button (the one with the "*" in it).
- Make sure "selection" is hi-lited.
- In the Find field, type \r\n (carriage return/linefeed)
- Type '\r\n,' in the Replace field (including the single quotes.
- Click the right button to replace them all.
- Fix the blank row at the bottom and the first row of data and you're done.
Here's the "before" example...
And here's the after (except without the comments). You just need to fix the first and last lines in the list. You could certainly make a regex string do it all for ya but it's more difficult to remember unless you use regex a whole lot.
Yep, totally agreed but not so fun dragging down a couple of thousand rows. 
add shift + alt, mouse click at bottom then click top target. done ( 
)
Well, I'll be damned. Nice trick. Ah... but there's a fly in the ointment... look at the examples in the screen shots I posted. Not all the values are the same width. The RegEx method handles that... the Shift/Alt/Click method will only handle the left side of those.
Still really good to know so thanks for the tip.
2k lines of data to be treated as individual strings is correct.
JeffModen
I followed the instructions step by step and this error appeared:
the other command I have tried is:
find: ([?[0-9][0-9][0-9][0-9][0-9][0-9][0-9])]
replace: '\1',
which works in word except places the last quotation the wrong way round. I have also tried with the on the ~ key but to no joy !
I've had success with the Shift + Alt approach - thank you all.
Another regex option:
Find: (\S+)
Replace: ,'\1'
The first and last lines may need fixing, but the regex will capture all consecutive non-whitespace characters and enclose them in single quotes with a leading comma.
1 Like
Nope, you can also click to the right of the widest string
If you copied from one of "those" sources, it may be that you only need to look for \n. However, did you actually turn on RegEx in the search box? A lot of people forget to do so or they have too small a section of the data selected when SELECTION is selected instead of CURRENT DOCUMENT. Take another look. It is one of those things very worth while to learn especially for when you're in the "heat of battle" someday in the near future.
Try that on these as see why I say, "Nope.... doesn't do what needs to be done.
O254
C787
R57
U930
X596
W762
K667
L704
O767
V91
Ok... so what did I do wrong?
[EDIT]
Ok... with no help from the horrible MS documentation on the subject, I figured that out, finally...
1 Like
Ugh, sorry, I forgot that they use $ instead of \ now. I don't know why they didn't just use Perl regex like EVERY OTHER library does.
And a few versions back they used {} instead of () to capture groups of characters, but at least fixed that. I had to completely redo a presentation after I upgraded SSMS.
Absolutely NP... In fact, that's what I love about "standards"... there are so many of them to chose from! 