?
Solved

Trim Characters using SQL statement

Posted on 2003-03-26
19
Medium Priority
?
2,370 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
  • 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
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.

 
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 70

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 70

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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Suggested Courses

615 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