'How to remove the first word from a large string

I want to remove the first word from a large string in SQL Server.

Input string:

931078027 BP 16:20:0:13 25 BAG 'B' CLASS

Desired output:

BP 16:20:0:13 25 BAG 'B' CLASS
sqlsql-servertsql


Solution 1:[1]

You may use a substring operation, e.g.

WITH yourTable AS (
    SELECT '931078027 BP 16:20:0:13 25 BAG ''B'' CLASS' AS val
)

SELECT val,
       SUBSTRING(val,
                 CHARINDEX(' ', val) + 1,
                 LEN(val) - CHARINDEX(' ', val)) AS val_out
FROM yourTable;

Demo

Solution 2:[2]

By using STUFF() function.

SQL

DECLARE @input VARCHAR(MAX) = '931078027 BP 16:20:0:13 25 BAG ''B'' CLASS';

SELECT @input AS input
    , STUFF(@input, 1, CHARINDEX(SPACE(1), @input), '') AS [output];

Output

+------------------------------------------+--------------------------------+
|                  input                   |             output             |
+------------------------------------------+--------------------------------+
| 931078027 BP 16:20:0:13 25 BAG 'B' CLASS | BP 16:20:0:13 25 BAG 'B' CLASS |
+------------------------------------------+--------------------------------+

Solution 3:[3]

I believe most easy way is to use RIGHT method to get it.

SELECT RIGHT(ColumnName, CHARINDEX(' ', ColumnName) - 1) FROM TableName

Solution 4:[4]

SELECT SUBSTRING(columnname, charindex(' ', columnname),LEN(columnname)-charindex(' ', columnname)+1) from tablename

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Tim Biegeleisen
Solution 2 Yitzhak Khabinsky
Solution 3 AsIndeed
Solution 4 VIPAN SABHERWAL

Related Questions