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

Question

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.

Muhammad Imran Ansari · 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';

Amit Mohanty · Answer

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

Muhammad Imran Ansari · Answer

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.

Baibhav Kumar · Answer

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;

Pinku · Answer

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

Pinku · Answer

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'