Query to remove suffix's from data in a field

The data in my field looks like this:
12345J
5308799J
8541255A
777464864
5598712N
963254

What I would like to do is  strip off the suffix's so that fthe data looks like this:
12345
5308799
8541255
777464864
5598712
963254

So when ever the code or query finds a alpha character at the end of the string it will remove it.
Any help is appreciated.

donnie91910Asked:
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.

pritamduttCommented:
Quick Question:

Is the suffix of fixed length?
0
pritamduttCommented:
If yes you could do something like
select left(<string>,len(<string)-1)

Open in new window

0
hnasrCommented:
If suffix a one character at end:
Try this for table:a (adesc)

SELECT a.adesc, IIf(Asc(Right([adesc],1))>Asc("9"),Left([adesc],Len([adesc])-1),[adesc]) AS d
FROM a;
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Kevin CrossChief Technology OfficerCommented:
Try this:

;WITH cte(txt) AS (
   SELECT '12345J'
   UNION SELECT '5308799J'
   UNION SELECT '8541255A'
   UNION SELECT '777464864'
   UNION SELECT '5598712N'
   UNION SELECT '963254'
)
SELECT LEFT(txt, LEN(txt)-PATINDEX('[^0-9]%', REVERSE(txt))) AS TrimTxt
FROM cte
;

Replace txt with your actual string column and cte with your actual table name. You won't need the ;WITH cte AS (...) part.

Kevin
0
Kevin CrossChief Technology OfficerCommented:
I just caught that you have this tagged to VBScript. My code is for SQL Server.
0
donnie91910Author Commented:
The suffix is of fixed length.
So the code or query should look for one alpha character (suffix) at the end of the string, and if it does find an alpha character then to remove it.  Is that possible?

This an Access database.
0
pritamduttCommented:
just run the following query

select val('5598712N')

Open in new window


where you can use your input strings
0
donnie91910Author Commented:
The input strings are in a field in a table and there are about 1000.
0
pritamduttCommented:
so the query would be something like

Select VAL(myTable.FieldName) from myTable

Open in new window


Please try this.

Regards,
0
kamalranjanCommented:
Hey PD. Is that you??
0
donnie91910Author Commented:
this code will strip off the suffix's whem they are  alpha ?
Select VAL(myTable.FieldName) from myTable

thanks.
0
Kevin CrossChief Technology OfficerCommented:
Yes, if this is MS Access, you can use VAL() to return the numerical portion of the string. Be careful with leading zeros as they will be removed.

To be safe, you can also consider:
SELECT LEFT(your_column, LEN(your_column)-1) & VAL(RIGHT(your_column, 1))
FROM your_table
;

That way you retain leading zeros.
0
pritamduttCommented:
hey kamal good to find u here...
0
pritamduttCommented:
So donnie is your problem addressed? so still some issues or doubts clouding up??

0
donnie91910Author Commented:
testing against the data.
0
donnie91910Author Commented:
The code takes off the Alpha character but then it replaces it with a 0 (zero).

Was: P2676J
Now: P26760
I need it to look like:P2676


Was: P235259J
Now: P2352590
I need it to look like:P235259


This is the code i am using:
SELECT LEFT(your_column, LEN(your_column)-1) & VAL(RIGHT(your_column, 1))
FROM your_table

thanks.
0
pritamduttCommented:
Here is the right code to achieve what u desire...


SELECT LEFT(your_column, 1) & VAL(RIGHT(your_column, LEN(your_column)-1))
FROM your_table

Open in new window



Regards,
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
donnie91910Author Commented:
Worked great!! Thanks
0
Kevin CrossChief Technology OfficerCommented:
Okay. What is happening is VAL() is defaulting to 0 when the value is not numeric. I did not notice that in testing. Here is what you want. It uses IIF(). If it works, I would credit: http:#36960019 -- the concept is the same.

LEFT(your_column, LEN(your_column)-1) & IIF(IsNumeric(RIGHT(your_column, 1)), RIGHT(your_column, 1), "")

You can see the similarity to the linked comment if you make it:

IIF(IsNumeric(RIGHT(your_column, 1)), LEFT(your_column, LEN(your_column)-1) , your_column)

Only difference is I use IsNumeric() versus ASCII comparison.
0
Kevin CrossChief Technology OfficerCommented:
But doesn't that strip out zeros?
Try "P000259J" and see if the result is what you want. I thought that was the whole point why we started down a different path?
0
Kevin CrossChief Technology OfficerCommented:
Never mind, I see you accepted hnasr's comment also. I did not scroll up. I think that will probably be the best approach long term to avoid getting bad results from casting string to integer.

Best regards and happy coding,

Kevin
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
Query Syntax

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.