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