help in SQL function

mark_tim1999 · #1 (**permalink**) 02-17-04, 12:40

Can anyone help in DB2 SQL?

DEPT_ID(CHAR data type)
2000
2100
2200_ABC
2300_CDE
2400_FGH

I need only numerics to be displayed(all characters aftre underscore should be truncated)?TRUNCATE does'nt work.

n_i · #2 (**permalink**) 02-17-04, 12:48

Quote:

Originally posted by mark_tim1999
Can anyone help in DB2 SQL?

DEPT_ID(CHAR data type)
2000
2100
2200_ABC
2300_CDE
2400_FGH

I need only numerics to be displayed(all characters aftre underscore should be truncated)?TRUNCATE does'nt work.

SUBSTR(DEPT_ID,1,5)

or

CASE
LOCATE(DEPT_ID, '_')
WHEN 0
DEPT_ID
ELSE
SUBSTR(DEPT_ID, 1, LOCATE(DEPT_ID, '_'))
END

mark_tim1999 · #3 (**permalink**) 02-17-04, 13:59

Thanks n_i

Internet Services

Web Development

Digital Marketing

Consumer Tech