topic Re: Need SQL Query to get Alphanumeric values in Talend Studio

Need SQL Query to get Alphanumeric values

nbabu228 — Fri, 15 Nov 2024 22:23:18 GMT

Hi everyone! Can anyone please tell me the SQL query to get only alphanumeric data of for Particular column... I want to exclude all numeric values data, and which is having astric also for e.g., :(1111.111,12345.12345,001-0042-0.2,001-0224. *******,020-0484. *******).

I want only the data which is combination of Alphanumeric for e.g., :(47S35A-5D.AAU,47LS5A-5D. AUSHX). Thanks in Advance! Have a Good day...

Re: Need SQL Query to get Alphanumeric values

Anonymous — Tue, 08 Nov 2022 18:02:20 GMT

I am afraid this isn't clear. Can you give examples of what you will start with and what you want to end with? Also, this won't require a SQL query, you will be able to do this with a bit of Java.

Re: Need SQL Query to get Alphanumeric values

nbabu228 — Wed, 09 Nov 2022 11:54:21 GMT

Hi rhall

Thanks for u response iam sharing the file with u.. which data is required for me and how the data is getting from MYSQL_DB. We have to use query only to filter this data.Please help me out.. Thanks in Advance!

Re: Need SQL Query to get Alphanumeric values

InfoCraft — Thu, 10 Nov 2022 16:55:01 GMT

Hi @nagendra babu,

Try Something like this(if you are using MYSQL):

SELECT * FROM tableName

WHERE columnName REGEXP '^[0-9]+[a-zA-Z.-]+$'

or columnName REGEXP '^[a-zA-Z]+[0-9.-]+$';

Kind regards,

😷19.

Re: Need SQL Query to get Alphanumeric values

nbabu228 — Mon, 21 Nov 2022 15:11:11 GMT

Hii Thanks for Response. But by using the above Iam not getting the required data. Mentioning the examples of the data what i need Exactly...

Required data (EG: ANCY.HRGK, BJKL.MNJY). Existing data in DB (EG:1111.12345,11245.78891, ANCY.HRGK, BJKL.MNJY, 1111.AHKJY).

By using the above query Iam only able to get (1111.AHKJY). Please help me on this... Thanks in Advance!!

Re: Need SQL Query to get Alphanumeric values

Anonymous — Mon, 21 Nov 2022 18:39:37 GMT

Hello @nagendra babu ,

Unfortunately your examples aren't clear. I can only continue the idea what @not specified not specified suggested.

We have this dataset:

1111.111

12345.12345

001-0042-0.2

001-0224. *******

020-0484. *******

111.12345

11245.78891

1111.AHKJY

47S35A-5D.AAU

47LS5A-5D.AUSHX

ANCY.HRGK

BJKL.MNJY

You want to have only ANCY.HRGK and similar?

^[a-zA-Z]+\.[a-zA-Z]+$

You want to EXCLUDE these?

12345.12345

001-0042-0.2

001-0224. *******

columnName NOT REGEXP '^[0-9\-]+\.[0-9a-zA-Z \*]+$'

But this will still keep:

47LS5A-5D.AUSHX

ANCY.HRGK

You can test the regular expressions over at: https://regex101.com/r/AzcOh2/1 (There's an explanation on the right side so its easier to understand.)

!! When copying to Talend / Java you must escape the escape character. So inside a java String the \- becomes \\-

Hope this helps.

Regex is really powerful but requires a bit of learning.