STRING

to_comma

This function add comma character according to 1000 scales.

Return type of value is VARCHAR2

select h.to_comma(4232153970)  from  dual 
H.TO_COMMA(4232153970)
----------------------------
4,232,153,970

to_percent

This function add % value in integer values.

Return type of value is VARCHAR2

select h.to_percent(0.47)  from  dual 
H.TO_PERCENT(0.47)
----------------------------
47%

This function generates % value between two user inputted values.

Return type of value is VARCHAR2

Usage

1 - abs((first number-second number)/first number)

select h.to_percent(5,3)  from  dual 
H.TO_PERCENT(5,3)
----------------------------
40%
select h.to_percent(5,10)  from  dual 
H.TO_PERCENT(5,10)
----------------------------
100%
select h.to_percent(10,5)  from  dual 
H.TO_PERCENT(10,5)
----------------------------
50%

to_col

This function concatenates rows to a single column.

Usage

to_col('tablename', 'concatenate-column', 'ordered-column', 'key-column', key-column-value)

					     select col1,col2 from test2
----------------------------------- A sales B developer
select col3,col4 from test
----------------------------------- A 20150621 A 20150721 B 20150521 B 20150520
select col1,col2 ,h.to_col('test','col4','col4','col3',col1) newcol  from  test2 
---------------------------------------- A sales 20150621,20150721 B developer 20150520,20150521

to_substr

This function substring without specific position.

Default direction is left and left

select h.to_substr('C:\Users\Justin\Desktop\HAD','\')  from  dual 
H.TO_SUBSTR('C:\USERS\JUSTIN\DESKTOP\HAD','\')
----------------------------
C:

In case of direction is left and left

select h.to_substr('C:\Users\Justin\Desktop\HAD','\',h.c_ll)  from  dual 
H.TO_SUBSTR('C:\USERS\JUSTIN\DESKTOP\HAD','\',H.C_LL)
----------------------------
C:

In case of direction is left and right

select h.to_substr('C:\Users\Justin\Desktop\HAD','\',h.c_lr)  from  dual 
H.TO_SUBSTR('C:\USERS\JUSTIN\DESKTOP\HAD','\',H.C_LR)
----------------------------
Users\Justin\Desktop\HAD

In case of direction is right and right

select h.to_substr('C:\Users\Justin\Desktop\HAD','\',h.c_rr)  from  dual 
H.TO_SUBSTR('C:\USERS\JUSTIN\DESKTOP\HAD','\',H.C_RR)
----------------------------
HAD

In case of direction is right and left

select h.to_substr('C:\Users\Justin\Desktop\HAD','\',h.c_rl)  from  dual 
H.TO_SUBSTR('C:\USERS\JUSTIN\DESKTOP\HAD','\',H.C_RL)
----------------------------
C:\Users\Justin\Desktop

to_reverse

This function reverse string or number at PL/SQL.

select h.to_reverse(123456789)  from  dual 
H.TO_REVERSE(123456789)
-----------------------
987654321
select h.to_reverse('abcdefg')  from  dual 
H.TO_REVERSE('ABCDEFG')
-----------------------
gfedcba

to_split (table)

This function return table type with user defined delimiter.

Usage

Case 1 : Split with user defined length of character

select valdata from table(h.to_split('123.456.789.abc.def.ghi',4)) 
123.
456.
789.
abc.
def.
ghi

Usage

Case 2 : Split with user defined character, Limited parameter count is 3.

select valdata from table(h.to_split('123.456.789.abc.def.ghi','.')) 
123
456
789
abc
def
ghi
select valdata from table(h.to_split('123.456.789.abc.def.ghi','abc')) 
123.456.789.
.def.ghi
select valdata from table(h.to_split('abcde0fgh2ijk3lmnopqrstu','0','2','3')) 
abcde
fgh
ijk
lmnopqrstu

to_ascii

This function convert to ASCII from string.

select h.to_ascii('A')  from  dual 
H.TO_ASCII('A')
-----------------
65

This function convert to ASCII from oct(8),decimal(10),hex(16)

select h.to_ascii('1A',16)  from  dual 
H.TO_ASCII('1A',16)
-----------------
26

to_hex

This function convert to HEX from integer.

select h.to_hex(26)  from  dual 
H.TO_HEX(26)
-----------------
1A

to_bin

This function convert to binary from integer.

select h.to_bin(26)  from  dual 
H.TO_BIN(26)
-----------------
11010

to_trans

This function convert large number to pretty string.

select h.to_trans(1200)  from  dual 
H.TO_TRANS(1200)
-----------------
1.2K
select h.to_trans(1200000)  from  dual 
H.TO_TRANS(120000)
-----------------
1.2M
select h.to_trans(1200000000)  from  dual 
H.TO_TRANS(1200000000)
-----------------
1.2B

Usage

This Translate function show pretty string according to your language type

Also support only English,Chinese,Korean,Japanese

to_trans ( base date, posting date, language type)

select h.to_trans(sysdate, sysdate,h.c_en) from  dual 
now
select h.to_trans(sysdate, sysdate,h.c_ch) from  dual 
現在
select h.to_trans(sysdate, sysdate,h.c_kr) from  dual 
현재
select h.to_trans(sysdate, sysdate-1/24/60,h.c_en) from  dual 
1minutes ago PM 07:36:02
select h.to_trans(sysdate, sysdate-1/24/60,h.c_ch) from  dual 
1分前 午後 07:36:16
select h.to_trans(sysdate, sysdate-1/24/60,h.c_kr) from  dual 
1분전 오후 07:36:28
select h.to_trans(sysdate, sysdate-1/24,h.c_en) from  dual 
1hours ago PM 06:38:21
select h.to_trans(sysdate, sysdate-1/24,h.c_ch) from  dual 
1時前 午後 06:38:11
select h.to_trans(sysdate, sysdate-1/24,h.c_kr) from  dual 
1시전 오후 06:37:53
select h.to_trans(sysdate, sysdate-1,h.c_en) from  dual 
1days ago PM 07:38:46
select h.to_trans(sysdate, sysdate-1,h.c_ch) from  dual 
1日前 午後 07:38:55
select h.to_trans(sysdate, sysdate-1,h.c_kr) from  dual 
1일전 오후 07:39:05
select h.to_trans(sysdate, sysdate-15,h.c_en) from  dual 
2weeks ago PM 07:40:08
select h.to_trans(sysdate, sysdate-15,h.c_ch) from  dual 
2週前 午後 07:39:59
select h.to_trans(sysdate, sysdate-15,h.c_kr) from  dual 
2주전 오후 07:39:49
select h.to_trans(sysdate, sysdate-65,h.c_en) from  dual 
2months ago PM 07:40:32
select h.to_trans(sysdate, sysdate-65,h.c_ch) from  dual 
2月前 午後 07:40:42
select h.to_trans(sysdate, sysdate-65,h.c_kr) from  dual 
2월전 오후 07:40:52
select h.to_trans(sysdate, sysdate-367,h.c_en) from  dual 
1years ago PM 07:41:52
select h.to_trans(sysdate, sysdate-367,h.c_ch) from  dual 
1年前 午後 07:41:40
select h.to_trans(sysdate, sysdate-367,h.c_kr) from  dual 
1년전 오후 07:41:29

add_key

This function add user defined character, even if special character like tab, enter, single quotes...

select h.add_key('TestText','A','xt',4,h.c_right)  from  dual 
H.ADD_KEY('TESTTEXT','A','XT',4,H.C_RIGHT)
-----------------
TestTextAAAA
select h.add_key('TestText','A','Te',4,h.c_left)  from  dual 
H.ADD_KEY('TESTTEXT','A','TE',4,H.C_LEFT)
-----------------
AAAATestText
select h.add_key('TestText',h.c_enter,4,h.c_left)  from  dual 
H.ADD_KEY('TESTTEXT',H.C_ENTER,4,H.C_LEFT)
-----------------




TestText

Usage

You can use this function as below input parameter

add_key (p_str in varchar2,p_val in varchar2,p_key in varchar2,p_count in h$type.int_t,p_direction in varchar2)

add_key (p_str in varchar2,p_val in varchar2,p_key in varchar2,p_count in h$type.int_t)

add_key (p_str in varchar2,p_val in varchar2,p_key in varchar2,p_direction in varchar2)

add_key (p_str in varchar2,p_val in varchar2,p_count in h$type.int_t,p_direction in varchar2)

add_key (p_str in varchar2,p_val in varchar2,p_count in h$type.int_t)

add_key (p_str in varchar2,p_val in varchar2,p_key_or_direction in varchar2)

Constraint value Description Value
H.C_TAB Tab character CHR(9)
H.C_ENTER Enter character CHR(10)
H.C_SQUOT Single quotation character CHR(34) '
H.C_AND AMPERSAND Character CHR(38) &

add_pattern

This function add user defined character or number according to user defined pattern(position).

select h.add_pattern('abcabcabc','T',2)  from  dual 
H.ADD_PATTERN('ABCABCABC','T',2)
-----------------
aTbcTabTcaTbc
select h.add_pattern('abcabcabc','T',2,h.c_right)  from  dual 
H.ADD_PATTERN('ABCABCABC','T',2,H.C_RIGHT)
-----------------
abTcaTbcTabTc
select h.add_pattern(123456789,7,2)  from  dual 
H.ADD_PATTERN(123456789,7,2)
-----------------
1723745767789

counta

This function return number of user defined character or language type.

select h.counta('abcabcabc','a)  from  dual 
H.COUNTA('ABCABCABC','A')
-----------------
3
select h.counta('abcabcabc','T',2,h.c_en)  from  dual 
H.COUNTA('ABCABCABC',H.C_EN)
-----------------
9
Constraint value Description Value
H.C_EN Character of English ENGLISH
H.C_CH Character of Chinese CHINESE
H.C_KR Character of Korean KOREAN
H.C_JR Character of Japanese JAPANESE
H.C_SPC Character of Special SPECIAL
H.C_NUM Character of Number NUMBER

chr_list (Table)

This function return list of character code according to user defined range.(default is 1~100)

select keydata,valdata from table(h.chr_list(33,100)) dual 
KEYDATA,VALDATA
------------------------
33 ! 34 " 35 # 36 $ 37 % 38 & 39 ' 40 ( 41 ) 42 * ... ... 100 d

Usage

You can use this function as below input parameter

chr_list ()

chr_list (p_end in number)

chr_list (p_start in number,p_end in number)