I have a string which is in 99999 124 fdsg format.
I want to replace the first space with - and the next space with a non-space. My expected result will look like this:
99999-124fdsg
To replace the first char I used this expression:
stuff(Product,charindex('',product),1,'-')as trim
Because I want to name the new column as trim. How can I do for the second character?
Charindex has last parameter start_location use it to search for second empty string
Try this
DECLARE @str VARCHAR(50) = '99999 124 fdsg'
SELECT Stuff(Stuff(@str,Charindex(' ',@str),1,'-'),Charindex(' ',@str,Charindex(' ',@str) + 1),1,'')
Result : 99999-124fdsg
Assuming, that you example with STUFF works correctly for the first blank you can just wrap this with REPLACE. After STUFF there's only the other blank left:
replace(stuff(Product,charindex(' ',product),1,'-'),' ','')
Working example:
DECLARE @s VARCHAR(50) = '99999 124 fdsg';
select replace(stuff(@s,charindex(' ',@s),1,'-'),' ','')
The result
99999-124fdsg