How to remove unknown characters using T-SQL?

Viewed 2612

I try to do a script to eliminate the Chinese characters in a string and between the Chinese characters I want to remove. Please see the example below. Thank you.

select LTRIM(SUBSTRING('Tower 6A 第6A座', PATINDEX('%[a-zA-Z0-9]%', 'Tower 6A 第6A座'), LEN('Tower 6A 第6A座')))
select LTRIM(REPLACE(SUBSTRING('Tower 6A 第6A座', CHARINDEX(N'樓', 'Tower 6A 第6A座') + 1, LEN('Tower 6A 第6A座')), ' ', ''))

Sample string:

Tower 6A 第6A座
Tower 3 第3座

Bad Result:

Tower 6A ?6A?
Tower6A?6A?
Tower 3 ?3?
Tower3?3?

Good result, I want to achieve:

Tower 6A
Tower 6A
Tower 3
Tower 3
5 Answers
Related