How to get first word before space or any special character in sql

Pinku

I have 2 condition . i want to get the first word beofre (space or any other special character) and other condition is to get the rest of the word
let consider string in title like @Title= 'sustainability-energy' or @Title= 'sustainability energy'

DECLARE @TitleDescFirst AS VARCHAR(max)=SUBSTRING(@Title,0,CHARINDEX(' ',@Title,0))
DECLARE @TitleDescSecond AS VARCHAR(max)=SUBSTRING(@Title,CHARINDEX(' ',@Title,0) + 1, LEN(@Title)-CHARINDEX(' ',@Title,0))

select @TitleDescFirst
Select @TitleDescSecond
I tried above but not getting quite correct result.Please help.It should work for both the condition.

6 Replies

Know the answer? Post it — somebody with the same question will find it here.

Muhammad Imran Ansari1y ago

Accepted answer

Hi Pinku,

The @DelimiterPos is the variable to calculate the provided string's PATINDEX (searching patterns within strings). You can remove the + 1 from the Amit reply from this line "ISNULL(NULLIF(PATINDEX('%[^a-zA-Z0-9]%', @Title), 0), LEN(@Title)) + 1" or can see the complete code here.

DECLARE @Title VARCHAR(MAX) = 'sustainability-energy'

DECLARE @DelimiterPos INT = PATINDEX('%[^a-zA-Z]%', @Title);

DECLARE @TitleDescFirst AS VARCHAR(MAX);
SET @TitleDescFirst = CASE 
    WHEN @DelimiterPos > 0 THEN SUBSTRING(@Title, 1, @DelimiterPos - 1)
    ELSE @Title
END;

DECLARE @TitleDescSecond AS VARCHAR(MAX);
SET @TitleDescSecond = CASE 
    WHEN @DelimiterPos > 0 THEN SUBSTRING(@Title, @DelimiterPos, LEN(@Title) - @DelimiterPos)
    ELSE ''
END;

SELECT @TitleDescFirst AS 'First Word';
SELECT @TitleDescSecond AS 'Remaining Text';

Baibhav Kumar1y ago

To extract the first word before a space or special character and the remaining part of the string, you can use the following approach for both cases:

-- For the first word before a space or special character
DECLARE @TitleDescFirst AS VARCHAR(MAX) = LEFT(@Title, PATINDEX('%[^a-zA-Z]%', @Title + ' ') - 1);

-- For the rest of the words after the first word
DECLARE @TitleDescSecond AS VARCHAR(MAX) = SUBSTRING(@Title, PATINDEX('%[^a-zA-Z]%', @Title + ' '), LEN(@Title));

SELECT @TitleDescFirst, @TitleDescSecond;

Pinku1y ago
Thanks Imran, But what is @Delimeter here.
I have @Title ='sustainability-energy'
created @TitleDescSecond to get the data. what is @DelimiterPos contain here?

Muhammad Imran Ansari1y ago

Hi Pinku,

As per the requirement, for the second word then you can modify the second query like:

DECLARE @TitleDescSecond AS VARCHAR(MAX);
SET @TitleDescSecond = CASE 
    WHEN @DelimiterPos > 0 THEN SUBSTRING(@Title, @DelimiterPos, LEN(@Title) - @DelimiterPos)
    ELSE ''
END;

Thank you.

Pinku1y ago
HI Amit ,
First word is correct but in second string it is showing like "energy" but i need "-energy". How to do this. If you can answer please reply. Thanks
So i need like if @title='sustainability-energy'

@titlefirst='sustainability'

@titlesecond=-energy'

If its like @title='sustainability energy'

@titlefirst='sustainability'

@titlesecond=energy'

If @title like @title='sustainability- :energy'

@titlefirst='sustainability'

@titlesecond=- :energy'

Amit Mohanty1y ago

Try this

DECLARE @Title VARCHAR(MAX) = 'sustainability-energy'

DECLARE @TitleDescFirst VARCHAR(MAX) = SUBSTRING(
    @Title, 1,
    ISNULL(NULLIF(PATINDEX('%[^a-zA-Z0-9]%', @Title), 0), LEN(@Title)) - 1
)
SELECT @TitleDescFirst

DECLARE @TitleDescSecond VARCHAR(MAX) = LTRIM(
    SUBSTRING(
        @Title,
        ISNULL(NULLIF(PATINDEX('%[^a-zA-Z0-9]%', @Title), 0), LEN(@Title)) + 1,
        LEN(@Title)
    )
)
SELECT @TitleDescSecond