Solved

# Query to remove suffix's from data in a field

Posted on 2011-10-12
Medium Priority
287 Views
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.

0
Question by:donnie91910
• 7
• 6
• 6
• +2

LVL 9

Expert Comment

ID: 36960011
Quick Question:

Is the suffix of fixed length?
0

LVL 9

Expert Comment

ID: 36960018
If yes you could do something like
``````select left(<string>,len(<string)-1)
``````
0

LVL 31

Assisted Solution

hnasr earned 668 total points
ID: 36960019
If suffix a one character at end:
Try this for table:a (adesc)

FROM a;
0

LVL 60

Assisted Solution

Kevin Cross earned 664 total points
ID: 36960025
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

LVL 60

Expert Comment

ID: 36960031
I just caught that you have this tagged to VBScript. My code is for SQL Server.
0

Author Comment

ID: 36960194
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

LVL 9

Expert Comment

ID: 36960205
just run the following query

``````select val('5598712N')
``````

where you can use your input strings
0

Author Comment

ID: 36960257
The input strings are in a field in a table and there are about 1000.
0

LVL 9

Expert Comment

ID: 36960267
so the query would be something like

``````Select VAL(myTable.FieldName) from myTable
``````

Regards,
0

LVL 2

Expert Comment

ID: 36960950
Hey PD. Is that you??
0

Author Comment

ID: 36961759
this code will strip off the suffix's whem they are  alpha ?
Select VAL(myTable.FieldName) from myTable

thanks.
0

LVL 60

Expert Comment

ID: 36962015
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

LVL 9

Expert Comment

ID: 36962428
hey kamal good to find u here...
0

LVL 9

Expert Comment

ID: 36962436
So donnie is your problem addressed? so still some issues or doubts clouding up??

0

Author Comment

ID: 36962513
testing against the data.
0

Author Comment

ID: 36962757
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

LVL 9

Accepted Solution

pritamdutt earned 668 total points
ID: 36962854
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
``````

Regards,
0

Author Closing Comment

ID: 36963145
Worked great!! Thanks
0

LVL 60

Expert Comment

ID: 36963181
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

LVL 60

Expert Comment

ID: 36963201
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

LVL 60

Expert Comment

ID: 36963225
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

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server.Â RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at: Â  htâ€¦
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Integration Management Part 2
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give iâ€¦
###### Suggested Courses
Course of the Month17 days, 13 hours left to enroll

#### 830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.