Solved

SQL Truncate

Posted on 2015-02-10
4
83 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
[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
  • 2
4 Comments
 
LVL 50

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 50

Accepted Solution

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

Open in new window

0
 
LVL 69

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 250 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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

688 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