Extract words from # separated string in SQL Server

Question

I have a requirement wherein I have to extract a # separated string as

Declare @Text nvarchar(255) ='Test1#Test2#Test3#Test4'

Declare @FirstWord  nvarchar(255) 
Decalre @SecondWord nvarchar(255)

@FirstWord will contain "Test1" and
@SecondWord will contain "Test2 Test3 Test4"

Possible duplicate of [Splitting the string in sql server](https://stackoverflow.com/questions/21428612/splitting-the-string-in-sql-server) — hatchet - done with SOverflow, Sep 28 '17 at 16:01
Also [How to split single column values to multiple column values](https://stackoverflow.com/questions/5123585/how-to-split-a-single-column-values-to-multiple-column-values) and [Sql Server split string with delimiter](https://stackoverflow.com/questions/41391179/sql-server-split-string-with-delimiter) — hatchet - done with SOverflow, Sep 28 '17 at 16:03
Please don't use the splitter found at the first link from @hatchet. Looping is not needed for string splitting. The Jeff Moden splitter is my choice, http://www.sqlservercentral.com/articles/Tally+Table/72993/ Here are some other excellent options. https://sqlperformance.com/2012/07/t-sql-queries/split-strings — Sean Lange, Sep 28 '17 at 16:03
@SeanLange - mainly I wanted to point out that the question has been asked and answered many times. Maybe one of the existing questions should be picked as the canonical one, and the best technique added as an answer, if it's not already here. Those were just the first three dupes of the question that a quick search found. — hatchet - done with SOverflow, Sep 28 '17 at 16:08
Some more relevant questions: [T-SQL split string](https://stackoverflow.com/questions/10914576/t-sql-split-string) (this seems to be the canonical question on stackoverflow), [How to separate string by comma using SQL Server?](https://stackoverflow.com/questions/45117042/how-to-separate-string-by-comma-using-sql-server), and [SQL Server: Split operation](https://stackoverflow.com/questions/2507330/sql-server-split-operation) — hatchet - done with SOverflow, Sep 28 '17 at 16:16

score 1 · Accepted Answer · answered Sep 28 '17 at 16:12

Declare @Text nvarchar(255) ='Test1#Test2#Test3#Test4'

Declare @FirstWord  nvarchar(255) = left(@Text,(CHARINDEX('#',@Text,1)-1))
Declare @SecondWord nvarchar(255) = replace(right(@Text,len(@Text) - (CHARINDEX('#',@Text,1))),'#', ' ')

select @FirstWord, @SecondWord

score 0 · Answer 2 · edited Sep 29 '17 at 10:23

DECLARE @TEXT NVARCHAR(255) = 'Test1#Test2#Test3#Test4'
,@FirstWord NVARCHAR(255)
,@SecondWord NVARCHAR(255)
,@index INT
,@LEN INT

SET @LEN = (
    SELECT LEN(@TEXT)
    )

SELECT @index = (
    SELECT CHARINDEX('#', @TEXT, 1)
    )

SET @FirstWord = (
    SELECT left(@TEXT, @index - 1)
    )
SET @SecondWord = REPLACE(RIGHT(@TEXT, @LEN - @index), '#', ' ')

SELECT @FirstWord AS FirstWord
,@SecondWord AS SecondWord

Extract words from # separated string in SQL Server

2 Answers2