Count the length of Chinese character in sql

I have a string that includes Chinese characters in it. I want to calculate the length of the string but I want to count 1 Chinese character equals to 3 characters.

Here are the strings and how I am counting length of strings.

SELECT LENGTH ('510-000897(PHCS-DS020G1EBONG-A231) on 0524 no cap ( all upper pin to 2.5mm , all') FROM dual

SELECT LENGTH ('510-000897(PHCS-DS020G1EBONG-A231)承認書.pdf') FROM dual

The first string returns length = 80 while the second string returns length = 41. But what I want is it should return length of second string = 47 as there are 3 Chinese characters in the string and 1 should be equal to 3 so the length should be 47.

Is it possible ?

Answer

If you want to length in Unicode characters you should use LENGTHC function.

SELECT LENGTHC('510-000897(PHCS-DS020G1EBONG-A231)承認書.pdf') FROM dual

LENGTHC('510-000897(PHCS-DS020G1EBONG-A231)?????????.PDF')
----------------------------------------------------------
                            47

Advertisement

Answer