?
Solved

Trim Characters using SQL statement

Posted on 2003-03-26
19
Medium Priority
?
2,362 Views
Last Modified: 2012-05-04
Here is my Example: P.O Box 123 (this can be a varied length) ie- P.O. BOX 123456 or po box 1233 or PO BOX ect..

I would like an SQL statement that would take this above example and trim off the po box portion along with spaces ect.. and leave only numbers.  In other words anything numeric after the word box, I would like to keep.  I am wondering if this can be done by simply looking for any numerics or somthing.  Thanks Very much in advance !!  

0
Comment
Question by:rrdiii2003
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 9
  • 7
  • 2
  • +1
19 Comments
 
LVL 1

Expert Comment

by:vjammy
ID: 8210179
You can try and use the right function, if your data is fixed length:

RIGHT
Returns the part of a character string starting a specified number of integer_expression characters from the right.

Syntax
RIGHT ( character_expression , integer_expression )

Arguments
character_expression

Is an expression of character data. character_expression can be a constant, variable, or column of either character or binary data.

integer_expression

Is the starting position, expressed as a positive whole number. If integer_expression is negative, an error is returned.

Return Types
varchar

character_expression must be of a data type that is implicitly convertible to varchar. Otherwise, use CAST to explicitly convert character_expression.


0
 

Author Comment

by:rrdiii2003
ID: 8210208
I am aware of the right function and it does not work because of the varied ways that users enter data in this field.  The sql will have to allow for varied lengths and extract only numerics.
0
 
LVL 23

Expert Comment

by:adathelad
ID: 8210268
Hi,

Try this:
DECLARE @pstrString VARCHAR(10)
SET @pstrString = 'P.O. BOX 1234'

SELECT LTRIM(RTRIM(RIGHT(@pstrString , LEN(@pstrString ) - PATINDEX('%box%', LOWER(@pstrString )) - 2)))

Cheers
0
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
LVL 23

Expert Comment

by:adathelad
ID: 8210275
I meant to DECLARE @pstrSing VARCHAR(20) *not* VARCHAR(10):

DECLARE @pstrString VARCHAR(20)
SET @pstrString = 'P.O. BOX 1234'

SELECT LTRIM(RTRIM(RIGHT(@pstrString , LEN(@pstrString ) - PATINDEX('%box%', LOWER(@pstrString )) - 2)))
0
 

Author Comment

by:rrdiii2003
ID: 8210401
adathelad,

Thanks for your quick response...  When I run this I get a incorrect syntax on the @pstrString.  How would I write this to look at table and field (still kind of new at this).

Thanks Again,
rrdiii2003  
0
 

Author Comment

by:rrdiii2003
ID: 8210419
adathelad,

I forgot to ask....When I use the DECLARE and SET, will that alter or make changes to data in my actual table?  I only want to run this using Query Analyzer and not make data changes.
0
 
LVL 23

Expert Comment

by:adathelad
ID: 8210422
SELECT LTRIM(RTRIM(RIGHT(YourFieldName, LEN(YourFieldName) - PATINDEX('%box%', LOWER(YourFieldName)) - 2)))
FROM YourTable

Just switch "YourFieldName" with the name of the field that contains the PO BOX value, and swap "YourTable" for the name of you table.

Cheers

0
 
LVL 23

Expert Comment

by:adathelad
ID: 8210463
> DECLARE @pstrString VARCHAR(20)
This declares a variable that I used in my example.

> SET @pstrString = 'P.O. BOX 1234'
This sets the value of that variable to an example value to use in the SELECT statement.

They do not alter the tables or data in any way.

0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 8210562
If someone's entering data unedited, in theory "box" could be misspelled from a typo.  You might want to base it on the first numeric character instead, like this:


SELECT SUBSTRING(yourColumnName, PATINDEX('%[0-9]%', yourColumnName), LEN(yourColumnName) - PATINDEX('%[0-9]%', yourColumnName) + 1)
FROM yourTableName
WHERE PATINDEX('%[0-9]%', yourColumnName) > 0
0
 

Author Comment

by:rrdiii2003
ID: 8210785
adathelad,

I get a message that reads: VARCHAR is not a recognized CURSOR option.
0
 
LVL 23

Expert Comment

by:adathelad
ID: 8210803
Can you post the exact code you are running?
0
 

Author Comment

by:rrdiii2003
ID: 8210905
adathelad,

Here it is ..

DECLARE column VARCHAR(20)
SET columnname = 'P.O. BOX 1234'

SELECT LTRIM(RTRIM(RIGHT(columnname, LEN(columnname) - PATINDEX('%box%', LOWER(columnname)) - 2))) from tablename
0
 
LVL 23

Expert Comment

by:adathelad
ID: 8210994
Ok....

---------------------------------------------------------
This example will not use a table of data. It uses a variable which contains 1 specific value (P.O. BOX 1234), which I posted to demontrate that my SELECT statement works.

DECLARE @column VARCHAR(20)
SET @column = 'P.O. BOX 1234'

SELECT LTRIM(RTRIM(RIGHT(@column, LEN(@column) - PATINDEX('%box%', LOWER(@column)) - 2)))

---------------------------------------------------------
---------------------------------------------------------
Now, if you want to select the values from a table:

SELECT LTRIM(RTRIM(RIGHT(YourColumn, LEN(YourColumn) - PATINDEX('%box%', LOWER(YourColumn)) - 2)))
FROM YourTable

I have assumed that you have a table called "YourTable", which has a column called "YourColumn" that contains the PO Box value. You need to change this SELECT statement to put in the actual table and column name you have.

So, if your table is called "Address" and the PB Box numbers are stored in a column called "POBox" it would be:

SELECT LTRIM(RTRIM(RIGHT(POBox, LEN(POBox) - PATINDEX('%box%', LOWER(POBox)) - 2)))
FROM Address
0
 

Author Comment

by:rrdiii2003
ID: 8211095
adathelad,

When I run this it still says VARCHAR is not a recognized CURSOR option ...  I am not sure what I am doing wrong

DECLARE address_box VARCHAR(20)
SET address_box = 'P.O. BOX 1234'

SELECT LTRIM(RTRIM(RIGHT(address_box, LEN(address_box) - PATINDEX('%box%', LOWER(address_box)) - 2)))
from address
0
 
LVL 23

Accepted Solution

by:
adathelad earned 100 total points
ID: 8211106
Read my last post. Do not use the DECLARE and SET statements. Just run:

SELECT LTRIM(RTRIM(RIGHT(address_box, LEN(address_box) - PATINDEX('%box%', LOWER(address_box)) - 2)))
from address
0
 

Author Comment

by:rrdiii2003
ID: 8211182
adathelad,

Bingo ! sorry it took me so long to "Get It"  That works!

Thanks Again
0
 

Author Comment

by:rrdiii2003
ID: 8211188
Very Helpful and patient with my many questions

Thanks Again !
0
 

Author Comment

by:rrdiii2003
ID: 8211359
ScottPletcher,

I have tried your statement and it works perfect as well!  How can I get you some points too?  Not sure on how to do this.

rrdiii2003
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 8211407
Thanks, I really do appreciate it!  But don't worry about the points.  Use whichever method you like better.  I was just trying to provide another way in case relying on "box" missed some of your rows.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

762 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