?
Solved

SQL Truncate

Posted on 2015-02-10
4
Medium Priority
?
97 Views
Last Modified: 2015-03-24
Hello.

I need to trim a column of phone numbers to remove spaces and truncate to 20 numbers.

Number range includes.

xxx xxxxxxxxx
xxxxxxxxxxxxx
xxxxx xxxxxxx
xxxx xxxx xxx

thanks.
0
Comment
Question by:aneilg
  • 2
4 Comments
 
LVL 53

Expert Comment

by:Vitor Montalvão
ID: 40600278
What is the data type of the field?
Which spaces do you want to remove (left, right, middle)?
Can you give us an example of current data and the expected results?
0
 

Author Comment

by:aneilg
ID: 40600356
Hello.

Data Type Varchar(23)

Its to remove any spaces in the phone field.

Now             xxx xxxxxx
Result          xxxxxxxxx

Now             xxxx xxxxx
Result          xxxxxxxxx

thanks.
0
 
LVL 53

Accepted Solution

by:
Vitor Montalvão earned 750 total points
ID: 40600367
You can use the REPLACE function:
SELECT REPLACE(PhoneNumberFieldName, ' ', '')

Open in new window

0
 
LVL 70

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 750 total points
ID: 40600787
SELECT LEFT(REPLACE(PhoneNumberFieldName, ' ', ''), 20) --and truncate to 20 "numbers"

Q: Do you need/want to check for specifically "numbers" in the column?  Do you want to strip all non-numerics?
0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

Question has a verified solution.

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

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Whether it be Exchange Server Crash Issues, Dirty Shutdown Errors or Failed to mount error, Stellar Phoenix Mailbox Exchange Recovery has always got your back. With the help of its easy to understand user interface and 3 simple steps recovery proced…
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…

850 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