How can we separate text and numbers from a column into two columns using COALESCE function in SQL server.
You can't use a COALESCE to do something like this. The COALESCE function is designed allow you to have alternate values if the primary value is NULL. So COALESCE(col1, col2, col3, 'default') returns the col1 value, except when that is NULL. When col1 is NULL it goes to col2 and does the same check, and so on.
So, I don't see a way for COALESCE to do this. Is the ProductId always in that format, with text followed by numbers? If that's the case you could use PATINDEX to find the location of the first number, and then use that result to split the string using SUBSTRING/LEFT/RIGHT functions.
I don't know why the heck you would want to do this like this, but here's a solution that partially includes COALESCE() to re-build the string, and might be what they were poking around for? Weird question
DECLARE @string varchar(100),@resultCol1 varchar(100),@resultCol2 varchar(100);
SET @string ='ABC1234'
SELECT @resultCol1 =COALESCE(@resultCol1,'')+SUBSTRING(@string,N,1)
ANDASCII(SUBSTRING(@string,N,1))BETWEEN 48 AND 57--numeric only
SELECT @resultCol2 =COALESCE(@resultCol2,'')+SUBSTRING(@string,N,1)
ANDASCII(SUBSTRING(@string,N,1))NOTBETWEEN 48 AND 57--non-numeric only
SELECT [col1] = @resultCol1,[col2] = @resultCol2;
20 People are following this question.
I am able to connect to SQL Server successfully without adding MultiSubnetFailover= True parameter in connection string in a multi sub environment with SQL AO. Unable to test the impact of this parameter.