developingprogrammer
asked on
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!! = ))
"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!! = ))
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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!! = ))
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!! = ))
ASKER
guys, i just realised that if i use the
StrReverse(Val(StrReverse(
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
StrReverse(Val(StrReverse(
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
-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