CODEWITHSUNDEEP
sqlsql-server
Baz Guvenkaya
Baz Guvenkaya

Reputation: 1572

SQL Server query to remove the last word from a string

There's already an answer for this question in SO with a MySQL tag. So I just decided to make your lives easier and put the answer below for SQL Server users. Always happy to see different answers perhaps with a better performance.

Happy coding!

Upvotes: 11

Views: 27201

Answers (7)

Victor Vinicius
Victor Vinicius

Reputation: 31

SELECT LEFT(username , LEN(json_path) - CHARINDEX('/', REVERSE(username ))+1) 
FROM Login_tbl 

UPDATE Login_tbl 
SET username = LEFT(username , LEN(json_path) - CHARINDEX('/', REVERSE(username ))+1)

Upvotes: 0

Viktor
Viktor

Reputation: 398

All the answers so far are actually about removing a character, not a word as the OP wanted.

In my case I was building a dynamic SQL statement with UNION'd SELECT statements and wanted to remove the last UNION:

DECLARE @sql NVARCHAR(MAX) = ''
/* populate @sql with something like this:
    SELECT 1 FROM dbo.T1 WHERE condition
    UNION
    SELECT 1 FROM dbo.T2 WHERE condition
    UNION
    SELECT 1 FROM dbo.T3 WHERE condition
    UNION
    SELECT 1 FROM dbo.T4 WHERE condition
    UNION
*/
-- remove the last UNION
SET @sql = SUBSTRING(@sql, 1, LEN(@sql) - PATINDEX(REVERSE('%UNION%'), REVERSE(@sql)) - LEN('UNION'))

Upvotes: 1

vaibhav borate
vaibhav borate

Reputation: 1

DECLARE @String VARCHAR(MAX) = 'One two three four'

SELECT LEFT(@String,LEN(@String)-CHARINDEX(' ', REVERSE(@String),0)+1)

Upvotes: -2

Alex M
Alex M

Reputation: 2548

Just an addition to answers.

The doc for LEN function in MSSQL:

LEN excludes trailing blanks. If that is a problem, consider using the DATALENGTH (Transact-SQL) function which does not trim the string. If processing a unicode string, DATALENGTH will return twice the number of characters.

The problem with the answers here is that trailing spaces are not accounted for. 

SELECT SUBSTRING(@YourString, 1, LEN(@YourString) - CHARINDEX(' ', REVERSE(@YourString)))

As an example few inputs for the accepted answer (above for reference), which would have wrong results:

   INPUT        ->   RESULT
   'abcd  '     ->   'abc'        --last symbol removed
   'abcd 123 '  ->   'abcd 12'    --only removed only last character

To account for the above cases one would need to trim the string (would return the last word out of 2 or more words in the phrase):

SELECT SUBSTRING(RTRIM(@YourString), 1, LEN(@YourString) - CHARINDEX(' ', REVERSE(RTRIM(LTRIM(@YourString)))))

The reverse is trimmed on both sides, that is to account for the leading as well as trailing spaces.

Or alternatively, just trim the input itself.

Upvotes: 3

Baz Guvenkaya
Baz Guvenkaya

Reputation: 1572

SELECT SUBSTRING(@YourString, 1, LEN(@YourString) - CHARINDEX(' ', REVERSE(@YourString)))

Edit: Make sure @YourString is trimmed first as Alex M has pointed out: 

SET @YourString = LTRIM(RTRIM(@YourString))

Upvotes: 14

mindbdev
mindbdev

Reputation: 444

DECLARE @String    VARCHAR(MAX) = 'One two three four'

SELECT LEFT(@String,LEN(@String)-CHARINDEX(' ', REVERSE(@String),0)+1)

Upvotes: 1

pavan kumar
pavan kumar

Reputation: 322

DECLARE @Sentence    VARCHAR(MAX) = 'Hi This is Pavan Kumar'

SELECT SUBSTRING(@Sentence, 1, CHARINDEX(' ', @Sentence) - 1) AS [First Word],
       REVERSE(SUBSTRING(REVERSE(@Sentence), 1, 
               CHARINDEX(' ', REVERSE(@Sentence)) - 1)) AS [Last Word]

enter image description here

Upvotes: 2

Related Questions