?
Solved

Query to remove suffix's from data in a field

Posted on 2011-10-12
21
Medium Priority
?
287 Views
Last Modified: 2012-08-13
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
Comment
Question by:donnie91910
  • 7
  • 6
  • 6
  • +2
21 Comments
 
LVL 9

Expert Comment

by:pritamdutt
ID: 36960011
Quick Question:

Is the suffix of fixed length?
0
 
LVL 9

Expert Comment

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

Open in new window

0
 
LVL 31

Assisted Solution

by:hnasr
hnasr earned 668 total points
ID: 36960019
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 60

Assisted Solution

by:Kevin Cross
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

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

Author Comment

by:donnie91910
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

by:pritamdutt
ID: 36960205
just run the following query

select val('5598712N')

Open in new window


where you can use your input strings
0
 

Author Comment

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

Expert Comment

by:pritamdutt
ID: 36960267
so the query would be something like

Select VAL(myTable.FieldName) from myTable

Open in new window


Please try this.

Regards,
0
 
LVL 2

Expert Comment

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

Author Comment

by:donnie91910
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

by:Kevin Cross
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

by:pritamdutt
ID: 36962428
hey kamal good to find u here...
0
 
LVL 9

Expert Comment

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

0
 

Author Comment

by:donnie91910
ID: 36962513
testing against the data.
0
 

Author Comment

by:donnie91910
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

by:
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

Open in new window



Regards,
0
 

Author Closing Comment

by:donnie91910
ID: 36963145
Worked great!! Thanks
0
 
LVL 60

Expert Comment

by:Kevin Cross
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

by:Kevin Cross
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

by:Kevin Cross
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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

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

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.

Join & Ask a Question