Removing characters that SQL does not seem to recognise-CodePudding

I have the following value in a SQL field ♡︎♈︎A͎l͎a͎n͎n͎a͎♈︎❤︎︎.

This is causing me grief as I am trying to export the dataset to a CSV using SSIS, but it's failing on this value.

Column type is NVARCHAR.

What I'm trying to do is use a regex to blank the field if it finds any non-Latin characters.

CASE WHEN firstname  NOT LIKE '%[^\w\d]%'  THEN ''

I've tried various versions of the regex, but nothing has worked.

My assumption is that the characters are so obscure, that SQL doesn't recognise them at all.

Does anyone have any thoughts about how I can blank this out? Just ignoring the row is not really an option as I suspect that there are others in the dataset.

Thanks

CodePudding user response：

Why do not substitute non-word character, using \W :

CASE WHEN firstname  NOT LIKE '%[\W]%'  THEN ''