How to get numeric portion of field using SQL

Hey guys, how do I get the numeric portion if the string using SQL? The data will look like this

"Physical1234"
"System1234"

Numbers are at the back.

P.S. what's the function that gets the numbers in front? And what's the formatting function that if you've put Len of 10, it will fill in your string and if your string is less than 10, it will fill in spaces for the remaining characters? I came across these 2 functions but forgot haha. Thanks guys!! = ))
developingprogrammerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

mbizupCommented:
<< what's the function that gets the numbers in front? >>

If the numbers are at the front, you can use Val(YourString)

SELECT Val(YourField) AS NumberPortion
FROM YourTable

<<  using SQL? >>

If the numbers are at the end, I believe you would have to create a user defined VBA function and call that from your query.
0
mbizupCommented:
<< And what's the formatting function that if you've put Len of 10, it will fill in your string  >>

Format("YourField", "0000000000")
0
IrogSintaCommented:
If you know that the numeric portion will always have 4 digits, you can use RIGHT(field,4).

For numbers in front that do not include a decimal point, you can use VAL(field)

To pad your numbers with up to 10 zeros, you can use Format(field, "0000000000").
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

IrogSintaCommented:
that do not include a decimal point
Correction... that includes up to one decimal point.

VAL(128.222.001.566NA) will produce 128.222
0
IrogSintaCommented:
Another method you can use if the number portion is at the end and the number of digits vary; use the Val and StrReverse function together:

StrReverse(Val(StrReverse(field)))
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jeffrey CoachmanMIS LiasonCommented:
Just a note here, you are really asking three questions here, ...not just one...
-How to get numeric portion of field using SQL
-what's the function that gets the numbers in front?
-what's the formatting function that if you've put Len of 10,

Pleas keep this in mind, as the rules here seek to keep the site to a one question per post format.

So while you did state "P.S.", ...you other concerns could may have been better if posted as to separate simple questions.

Sound fair?

JeffCoachman
0
developingprogrammerAuthor Commented:
thanks mbizup and IrogSinta!! whao really great stuff here!! = )) and IrogSinta the strreverse is absolutely fantastic!! i didn't even know it existed!! haha = ))

hi Jeff! yes what you're saying does sound fair 100%! my main question was on getting the numeric portion of the string, but then as i was typing i thought of the other 2 btw questions and i thought if those weren't answered that's ok as well. BUT..... you know the experts in EE are just too superb!! and those 2 questions got answered as well!! = )) so yes you're 100% right that i should have separated the questions and moving forward i will!! = ))
0
developingprogrammerAuthor Commented:
guys, i just realised that if i use the

StrReverse(Val(StrReverse(field))),

if my field is Physical10, the result will be 1.

because when we use the val function it treats 01 as 1 --> correctly so.

so i think maybe the best way is to use regex
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.