question

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.

Thanks Chris, I can do it using PATINDEX.

Actually it was asked to me during an interview. I told him that I can do it using PATINDEX but he was expecting the solution using COALESCE.

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)

FROM dbo.Tally

WHERE N<len(@string)+1

ANDASCII(SUBSTRING(@string,N,1))BETWEEN 48 AND 57--numeric only

SELECT @resultCol2 =COALESCE(@resultCol2,'')+SUBSTRING(@string,N,1)

FROM dbo.Tally

WHERE N<len(@string)+1

ANDASCII(SUBSTRING(@string,N,1))NOTBETWEEN 48 AND 57--non-numeric only

SELECT [col1] = @resultCol1,[col2] = @resultCol2;