Many solutions will break for any strings that have *less than 2 words*, which is increasingly likely for people hoping to parse the first *n* number of words.

Let's first look at the query, and then how we can tell if it actually evaluated correctly. 
For that, we need to nest multiple [`CHARINDEX`][charindex] statements, which take the following params:



<pre><code>CHARINDEX( expressionToFind , expressionToSearch [ , start_location ] )
</code></pre>

Charindex will return the *first* index where it finds the specific string. What we keep doing is kicking the ball down the road by adding in a `start_location` equal to the first found instance +1 so it'll find the 2nd, 3rd, 4th instance, etc. Also, instead of [`SUBSTRING(@str, 0,...`][substring] we can just use [`LEFT(@str,...`][left] to capture the first portion of the string, but calculating how far deep to go is the hard part anyway:




 DECLARE @string VARCHAR(1000) = 'One Two Three';
 SELECT LEFT(@string, CHARINDEX(' ', @string,
 CHARINDEX(' ', @string,
 CHARINDEX(' ', @string,
 CHARINDEX(' ', @string)+1)+1)+1))


But this will **fail** if we don't have the minimum number of words: 
Technically, it'll just keep looping around infinitely. Once it runs out of spaces, it'll start indexing again from the beginning

[![Char Index Demo][1]][fiddle]

**Pop quiz**... what will the above query resolve to? Hover below for answer
>! `SELECT LEFT('One Two Three', 3) -- 'One'`

In the solution in [Extracting a specific number of words from a string in sql][2], we can check that the last `CHARINDEX` produced a non-zero value, meaning it hit a space at that level of depth. But also, since the nested charindex is kind of unwieldy, we can get at that information at little more directly by [counting the number of occurrences of a certain substring in a SQL varchar?][3]

### So the final solution should look like this to grab the first *4 words*:



 DECLARE @string VARCHAR(1000) = 'One Two Three Four Five Six';
 
 SELECT CASE WHEN LEN(@string)-LEN(REPLACE(@string, ' ', '')) < 4 
 			THEN @string
 			ELSE LEFT(@string, CHARINDEX(' ', @string,
 CHARINDEX(' ', @string,
 CHARINDEX(' ', @string,
 CHARINDEX(' ', @string)+1)+1)+1))
 	 END

If there are less than 4 spaces, we'll just return the whole string. For more than four, we'll find the position of the 4th space and return the left portion of the string all the way to that position


[1]: https://i.stack.imgur.com/iSPPH.png
[2]: https://www.sqlservercentral.com/Forums/FindPost460865.aspx
[3]: https://stackoverflow.com/q/738282/1366033
[fiddle]: https://jsfiddle.net/KyleMit/cdppkpft/
[charindex]: https://learn.microsoft.com/en-us/sql/t-sql/functions/charindex-transact-sql
[substring]: https://learn.microsoft.com/en-us/sql/t-sql/functions/substring-transact-sql
[left]: https://learn.microsoft.com/en-us/sql/t-sql/functions/left-transact-sql

I have string like this "  This is a hello world example"

Now I want first two words of the sentence as my output in SQL Server. i.e. This is .

Another example:
Original sentence : "Complete word exercise"
Output: Complete word



First two words in the string - sql server

As @Hutch pointed out, one of the major limitations of `sp_who2` is that it does not take any parameters so you cannot sort or filter it by default. You can [save the results into a temp table][temp sp_who2], but then the you have to declare all the types ahead of time (*and remember to `DROP TABLE`*).

Instead, you can just go directly to the source on `master.dbo.sysprocesses`

I've constructed this to output almost exactly the same thing that `sp_who2` generates, except that you can easily add `ORDER BY` and `WHERE` clauses to get meaningful output.



 SELECT spid,
 sp.[status],
 loginame [Login],
 hostname, 
 blocked BlkBy,
 sd.name DBName, 
 cmd Command,
 cpu CPUTime,
 physical_io DiskIO,
 last_batch LastBatch,
 [program_name] ProgramName 
 FROM master.dbo.sysprocesses sp 
 JOIN master.dbo.sysdatabases sd ON sp.dbid = sd.dbid
 ORDER BY spid 

[temp sp_who2]: http://www.sqlmatters.com/Articles/sp_who2%20-%20filtering%20and%20sorting%20the%20results.aspx

How to get detailed list of connections to database in sql server 2005?

As Aaron Bertrand pointed out, you can use the undocumented `xp_regread` in SQL Server 2005 and SQL Server 2008, but there is a better way, starting with SQL Server 2008R2 SP1.

From the article [How to identify the SQL Server Service Account in T-SQL][1], you can use [`sys.dm_server_services`][2] like this:



 SELECT DSS.servicename,
 DSS.startup_type_desc,
 DSS.status_desc,
 DSS.last_startup_time,
 DSS.service_account,
 DSS.is_clustered,
 DSS.cluster_nodename,
 DSS.filename,
 DSS.startup_type,
 DSS.status,
 DSS.process_id
 FROM sys.dm_server_services AS DSS;

![SQL Service Account Query Results][3]

[1]: http://sqlity.net/en/1868/identify-sql-server-service-account-in-t-sql/
[2]: https://msdn.microsoft.com/en-us/library/hh204542.aspx
[3]: http://i.imgur.com/HdfsUZF.png

How can I get the Service Account name for the SQL Agent service for a particular SQL Server (SQL 2005).
Is it possible to get using SQL statements or WMI ?



Get Service Account Details of the SQL Agent Service

You can find trigger objects either on [`sys.objects`][1] view with an [`xtype`][2] = `'tr'` or, starting in SQL 2008, on the [`sys.triggers`][3] view.

You can get the definition for triggers (or any other object) by joining to [`sys.sql_modules.definition`][4] or by calling [`OBJECT_DEFINITION(object_id)`][5]. 
Just don't use [`sys.syscomments.text`][6] because it caps out at `nvarchar(4000)`

In any combination of the above (really doesn't matter) you could generate a single file to populate all of your scripts by terminating each definition with "Go" like this:



 SELECT m.definition, 'GO'
 FROM sys.triggers tr
 JOIN sys.sql_modules m ON tr.object_id = m.object_id

Just take the resulting output and save it in a single file and execute to recreate every trigger. 

### Generate Individual Scripts

The problem is a little more complex if you want to generate individual files for each script. 
...mostly in automating the file generation; the previous solution leans on just presenting data and letting you save it.

You can use [`bcp utility`][7], but I find SQL to be pretty clumsy at dealing with generating files. Alternatively, just grab the data in powershell and then generate files from there where you have a lot more fine tuned access and control. Here's a script that will grab all the triggers and create them into folders for each table.



 # config
 $server = "serverName"
 $database = "dbName"
 $dirPath = 'C:\triggers'
 
 # query
 $query = @"
 SELECT TOP 5
 t.name AS TableName, 
 tr.name AS TriggerName,
 m.definition As TriggerScript
 FROM sys.triggers tr
 JOIN sys.tables t ON tr.parent_id = t.object_id
 JOIN sys.sql_modules m ON tr.object_id = m.object_id
 "@
 
 # connection
 $sqlConnection = New-Object System.Data.SqlClient.SqlConnection
 $sqlConnection.ConnectionString = "Server=$server;Database=$database;Integrated Security=True"
 $sqlConnection.Open()
 
 # command
 $sqlCmd = New-Object System.Data.SqlClient.SqlCommand($query, $sqlConnection)
 $sqlCmd.CommandTimeout = 10 * 60 # 10 minutes
 
 
 # execute
 $reader = $sqlCmd.ExecuteReader()
 while ($reader.Read())
 {
 # get reader values
 $tableName = $reader.GetValue(0)
 $triggerName = $reader.GetValue(1)
 $triggerScript = $reader.GetValue(2)
 
 $filepath = "$dirPath\$tableName\$triggerName.sql"
 
 # ensure directory exists
 [System.IO.Directory]::CreateDirectory("$dirPath\$tableName\")
 
 # write file
 New-Item -Path $filepath -Value $triggerScript -ItemType File
 }
 $reader.Close()
 $sqlConnection.Close()

[1]: https://msdn.microsoft.com/en-us/library/ms177596.aspx
[2]: https://stackoverflow.com/q/16243857/1366033
[3]: https://msdn.microsoft.com/en-us/library/ms188746.aspx
[4]: https://msdn.microsoft.com/en-us/library/ms175081.aspx
[5]: https://msdn.microsoft.com/en-us/library/ms176090.aspx
[6]: https://msdn.microsoft.com/en-us/library/ms186293.aspx
[7]: https://msdn.microsoft.com/en-us/library/ms162802.aspx "Bulk Copy Program"

I would like to generate a script/extract all of the triggers I have in database, so that I can use them in another one. How can I do this?