SQL Query | How to Extract Numbers and Alphabets from an alphanumeric string | Translate function

This video discusses the SQL Interview Question

How to extract numbers and alphabets from an alpha numeric string

Solution is provided using SQL Translate function.

Datastage Extract Numbers and Alphabets

Please do not forget to like, subscribe and share.

For enrolling and enquiries, please contact us at

[autumnjensen]

Thank you so much! This is exactly what I was looking for!

[YO-xkbe]

Could we have used the Patindex function as well ?

[SEEMAKUMARI-gyil]

Thank you so much mam, it is very helpful video and please provide us create and insert query, it will help us for practice

[hv]

Thanks for posting this. What is the difference between replace and translate?

[kristyowens]

Great stuff. Please post the SQL query using PATINDEX as well.

[Motivationmindset]

Hi, How do you separate alphabets from this data for example A256-20G, A658-78L, G588-100ML, I just only pull unit only (G, ML, L). any solution?

[sarathchandra]

If this is the case we can simply use left and right operators in sql to seperate please solve the same when alphanumeric string is combined as'xas123ssh333'.

[chittibabu]

For the same data can we sperate alphanumeric by -
Eg: AB-34-HP

[ShivamGupta-wnmo]

select *,
regexp_replace(value, '[^a-zA-Z]', "" ) as alohabet,
regexp_replace(value, '[^0-9]', '') as numbers
from alphanumeric

simple way to use regexp

[daudjhoni]

nice.. how to extract this: drink#eat#drive#sleep, to output DrinkEatDriveSleep

[shazankhan]

Error -

The second and third arguments of the TRANSLATE built-in function must contain an equal number of characters.

[ramakrishnaravula]

Thank you for video, if name and number mixed like '45875RAMA4785KRISHNA'

[rajasekarp.v.]

Tablename-SQL
Column name-NAME
PRA9798VIN
RA556JA
VICK989Y

[Dream-eims]

nice, but how to count the number of letters in one word??