SQL that returns a numeric

Question

0.00/5 (No votes)

See more:

Hi
I want to retrieve a data that From substring with a numeric value

ex.

561-265-262665
656-01D-054548
355-2D0-000048
656-656-465656
636-412-112313

on my Query

SQL

SELECT Subtring(BCode,5,3) FROM Table

the result are

265
01D
2D0
656
636

How can I filter for the result of
265
656
636

I've tried REGEX but no luck :(

Posted 3-Sep-13 19:25pm

iMaker.ph

Add a Solution

5 solutions

Solution 1

This link will help you....

http://blog.sqlauthority.com/2008/10/14/sql-server-get-numeric-value-from-alpha-numeric-string-udf-for-get-numeric-numbers-only/[^]

Posted 3-Sep-13 20:10pm

Member 10240234

Solution 3

Hi iMaker,

try the below code, It's working for me.

SQL

SELECT SUBSTRING(Column1,5,3) FROM TableName WHERE SUBSTRING(Column1,5,3) not like '%[^0-9]%'

I hope this will help you.

Thank's
Mohan G

Posted 3-Sep-13 20:17pm

Mohan Gopi

Comments

iMaker.ph 4-Sep-13 2:33am

It doesn't work :(
anyway thank you :)

Mohan Gopi 4-Sep-13 2:54am

Hi iMaker, i tried this its working for me. Replace Column1 to BCode and TableName to Table accoding to your query.

iMaker.ph 4-Sep-13 5:53am

Why is it now working on MySQL? :( anyway I just add a function for this problem.

Solution 4

Hello,

Try bellow query...

Select BCODE FROM
(
Select SUBSTRING(BCode,5,3) as BCODE from Tbl1
)
as
A Where BCODE not like '%[a-z]%'

Posted 3-Sep-13 20:25pm

Shobhana.n

Comments

iMaker.ph 4-Sep-13 2:33am

It doesn't work :(
anyway thank you :)

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

iMaker.ph · Accepted Answer · 2013-09-03T20:13:00

Solution 2

This link help me :)

http://forums.mysql.com/read.php?60,1907,38488#msg-38488

Posted 3-Sep-13 20:13pm

iMaker.ph

Updated 3-Sep-13 20:14pm

v2

jayron527 · Accepted Answer · 2013-09-05T18:17:00

Solution 5

Just use REGEXP.......

SELECT Substring(BCode,5,3) FROM Table WHERE Substring(BCode,5,3) NOT REGEXP '[[:alpha:]]+'

Posted 5-Sep-13 18:17pm

jayron527