none
Quick key shortcut to apply single quotes? RRS feed

  • Question

  • Hi All, Do you know if there is a quick key or way inside of MSFT SQL Server MGMT Studio, Query writer to apply single quotes to a large string of text?

    4026, 4081, 4084, 4091, 4095, 4101, 4102, 4106, 4107, 4112, 4117, 4121, 4123, 4124, 4126, 4135, 4136, 4137, 4138, 4140, 4141, 4146, 4150, 4152, 4154, 4201, 4203, 4208, 4209, 4210, 4211, 4217, 4218, 4224, 4228, 4230, 4239, 4241, 4242, 4248, 4304, 4306, 4308, 4322

    to

    '4026', '4081', 4'084', '4091', '4095', '4101', '4102' ... etc.

    Thanks,

    :) john

    Monday, August 11, 2014 4:32 PM

Answers

  • Learn how to use Regular Expressions for Find and Replace in SQL Server Management Studio (SSMS).  It has the same capability as Visual Studio as they are built on the same shell.
    1. Within SSMS, in your query window, press Ctrl+H.  This should open the Find and Replace window
    2. Check the 'Use: Regular Expressions' checkbox
    3. In the 'Find what:' box type     :z  (no quotes).  This is RegEx shorthand for 'find an integer'
    4. In the 'Replace with' box, type '\0' (with the quotes).  This is RegEx shorthand for 'replace whatever you find with itself wrapped in quotes'
    5. Press 'Replace All'
    6. Double-check your results

    Note the version of RegEx in SSMS is slightly cutdown but works pretty well.

    HTH

    • Marked as answer by johnb22 Wednesday, August 13, 2014 2:07 PM
    Wednesday, August 13, 2014 9:39 AM

All replies

  • you could use find & replace? ctrl-f and then click on the replace tab, in the top box put in ", " and in the bottom box "', '" then click replace.

    Monday, August 11, 2014 4:43 PM
  • ya, that's what I ended up doing... but still wonder if there is a cleaner quick key to apply. I prob need to use the visual studio editor for that kind of voodoo :)
    Monday, August 11, 2014 4:45 PM
  • ya, that's what I ended up doing... but still wonder if there is a cleaner quick key to apply. I prob need to use the visual studio editor for that kind of voodoo :)

    Yep I wrote a quick win app with C# to create tuples for my SQL code, it comes in handy when a client gives me a horrid monsterous list of unformatted text.
    Monday, August 11, 2014 4:47 PM
  • I would do this in excel:

    1. Open Excel

    2. Go to Data--->Text To Columns---->Select Delimited--->Comma--->Finish (By this rows will be pasted into different cells)

    3. Copy these cells and Right click on any new cell you will see below option to transpose the rows.. Click on it:

    4. Lets say you have the first row 4026 in row AB5 so in the adjacent cell write this formula  ="'"&AB5&"',"

    If you have this in any other cell replace the CELLNO AB5 with your actual cell no.

    5. Double click at the RHS corner of the cell to drag this formula untill end of the row.

    By this you will get the single quote for each value in a column. If you want it in row, then again copy the values and do transpose.

    Hope this helps!


    If this post answers your query, please click "Mark As Answer" or "Vote as Helpful".

    Monday, August 11, 2014 4:49 PM
  • It looks to me like you can get the vast majority of them by replacing ", " with "', '" (ignoring the double quotes, of course)

    Monday, August 11, 2014 4:54 PM
    Moderator
  • Nice solution. I didn't think of using excel!
    Monday, August 11, 2014 4:56 PM
  • if you absolutely want to do it in management studio, you can do it via TSQL.

    select '''' + replace(@yourstring, ', ', ''', ''') + ''''

    It would replace all your coma space (, ) by (', ') and append a single quote at the beginning and end.

    Monday, August 11, 2014 4:57 PM
  • You can use the fnSplit function (Table-valued function) for tasks involving lists separated with comma or any character separated lists;

    Declare @vSTR varchar(1000)
    Set @vStr = '4026, 4081, 4084, 4091, 4095, 4101, 4102, 4106, 4107, 4112, 4117, 4121, 4123, 4124, 4126, 4135, 4136, 4137, 4138, 4140, 4141, 4146, 4150, 4152, 4154, 4201, 4203, 4208, 4209, 4210, 4211, 4217, 4218, 4224, 4228, 4230, 4239, 4241, 4242, 4248, 4304, 4306, 4308, 4322'
    
    Select '''' + Value + '''' from dbo.fnSplit(@vSTR,',')
    

    If you don have fnSplit, here is the code. This is a very useful function and can be used for future as well.

    /****** Object:  UserDefinedFunction [dbo].[fnSplit]    Script Date: 8/11/2014 10:03:31 AM ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    ALTER function [dbo].[fnSplit]
    (@pString nvarchar(max),@pSplitChar char(1))
    returns @tblTemp table (tid int,value varchar(1000))
    as
    begin
    	declare @vStartPosition		int
    	declare @vSplitPosition		int
    	declare @vSplitValue		varchar(1000)
    	declare @vCounter		int
    	set @vCounter=1
    	select @vStartPosition = 1,@vSplitPosition=0
    	set @vSplitPosition = charindex( @pSplitChar , @pString , @vStartPosition )
    	if (@vSplitPosition=0 and len(@pString) != 0)
    	begin
    		INSERT INTO @tblTemp
    			(
    			tid		,
    			value	
    			)
    		VALUES
    			(
    			1	,
    			@pString		
    			)
    		return		--------------------------------------------------------------->>
    	end
    	set @pString=@pString+@pSplitChar
    	while (@vSplitPosition > 0 )
    	begin
    		set @vSplitValue = substring( @pString , @vStartPosition , @vSplitPosition - @vStartPosition )
    		set @vSplitValue = ltrim(rtrim(@vSplitValue))
    		INSERT INTO @tblTemp
    			(
    			tid		,
    			value	
    			)
    		VALUES
    			(
    			@vCounter	,
    			@vSplitValue		
    			)
    		set @vCounter=@vCounter+1
    		set @vStartPosition = @vSplitPosition + 1
    		set @vSplitPosition = charindex( @pSplitChar , @pString , @vStartPosition )
    	end
    	return
    end


    Please visit my Blog for some easy and often used t-sql scripts
    My BizCard

    Monday, August 11, 2014 5:04 PM
  • I'm surprised this hasn't been mentioned.

    put each entity on its own line.

    Hold alt, and drag-select in front of them, you'll see the line be selected. Press the character you want to insert. Repeat at the other end.

    Monday, August 11, 2014 8:55 PM
  • Learn how to use Regular Expressions for Find and Replace in SQL Server Management Studio (SSMS).  It has the same capability as Visual Studio as they are built on the same shell.
    1. Within SSMS, in your query window, press Ctrl+H.  This should open the Find and Replace window
    2. Check the 'Use: Regular Expressions' checkbox
    3. In the 'Find what:' box type     :z  (no quotes).  This is RegEx shorthand for 'find an integer'
    4. In the 'Replace with' box, type '\0' (with the quotes).  This is RegEx shorthand for 'replace whatever you find with itself wrapped in quotes'
    5. Press 'Replace All'
    6. Double-check your results

    Note the version of RegEx in SSMS is slightly cutdown but works pretty well.

    HTH

    • Marked as answer by johnb22 Wednesday, August 13, 2014 2:07 PM
    Wednesday, August 13, 2014 9:39 AM
  • Nice, HTH... that may be the best option yet. This thread has been very enlightening. Thank you everyone for contributing!

    Wednesday, August 13, 2014 2:08 PM
  • I couldn't get this to work. Do you maybe have some preferences set that make this possible on your machine?
    Wednesday, August 13, 2014 2:09 PM
  • wBob's method is working with default SSMS settings on my machine.

    Thanks for sharing.

    Wednesday, August 13, 2014 2:30 PM
    Moderator
  • You can also do it programmatically:

    DECLARE @LargeStr varchar(max) = '4026, 4081, 4084, 4091, 4095, 4101, 4102, 4106, 4107, 4112, 4117, 4121, 4123, 4124, 4126, 4135, 4136, 4137, 4138, 4140, 4141, 4146, 4150, 4152, 4154, 4201, 4203, 4208, 4209, 4210, 4211, 4217, 4218, 4224, 4228, 4230, 4239, 4241, 4242, 4248, 4304, 4306, 4308, 4322'
    
    SELECT CHAR(39)+REPLACE(@LargeStr, ', ', CHAR(39)+', '+CHAR(39))+CHAR(39);
    
    -- '4026', '4081', '4084', '4091', '4095', '4101', '4102', '4106', '4107', '4112', '4117', '4121', '4123', '4124', '4126', '4135', '4136', '4137', '4138', '4140', '4141', '4146', '4150', '4152', '4154', '4201', '4203', '4208', '4209', '4210', '4211', '4217', '4218', '4224', '4228', '4230', '4239', '4241', '4242', '4248', '4304', '4306', '4308', '4322'


    Kalman Toth Database & OLAP Architect SQL Server 2014 Design & Programming
    New Book / Kindle: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2016








    Friday, August 15, 2014 11:49 PM
    Moderator
  • THIS IS EXACTLY WHAT I NEEDED! Thank you!
    Monday, February 13, 2017 4:11 PM