How to handle a special character that does not exist in SQL?

So using the CHAR(x) function you can display special characters. I have some imported data that is displaying with the following as a "?" or CHAR(63) / ASCII code 63 - this is incorrect, and I think SQL puts this character in when it doesn't recognise the actual character

select colA, colB,

  patindex('%[^ !-~]%' COLLATE Latin1_General_BIN,colB) as [Position],
  substring(colB,patindex('%[^ !-~]%' COLLATE Latin1_General_BIN,colB),1) as [InvalidCharacter],
  ascii(substring(colB,patindex('%[^ !-~]%' COLLATE Latin1_General_BIN,colB),1)) as [ASCIICode]
from  mytable
where patindex('%[^ !-~]%' COLLATE Latin1_General_BIN,colB) >0

So after some digging I find that the actual character in the data is the "Latin Small ligature FL" character essentially just the letters "fl" as one character.

I'm struggling to replace this though, as there is no CHAR(xx) code for this character in SQL.

EDIT - Also - just to add context - the data is from a web source and is somehow display this fl character, all I want to do is replace this character with actual "fl" characters! - This is to insert into an app and it is not displaying correctly once in CSV format (although my grid actually shows the "fl" character fine).

Any suggestions?

Thanks!

Solution

So finally managed to figure this one out after a lot of hunting down.

SELECT colb from mytable
where colb like  N'%ﬂ%'  COLLATE Latin1_General_BIN2

Gives me the data I need to change. Then doing this in the replace gives me:

REPLACE(colb COLLATE Latin1_General_BIN2, N'ﬂ', N'fl')