Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

SQL Server - SQL Help

Posted on 2011-05-03
5
Medium Priority
?
254 Views
Last Modified: 2012-05-11
Hello,

I have a field in my SQL that has dashes in it - 123-45-67. I want this field disaplyed in the report without dashes like 1234567. How can I do this? Can I use Replace funtion?

Please let me know.Thanks
0
Comment
Question by:rustypoot
5 Comments
 
LVL 29

Accepted Solution

by:
Paul Jackson earned 336 total points
ID: 35518078
yes replace can be used :

select replace(columnname, '-', '') as alias
from tablename
0
 
LVL 29

Assisted Solution

by:sammySeltzer
sammySeltzer earned 332 total points
ID: 35518089
You can also use a function:

CREATE FUNCTION udfFormatFormatPhone
	(@cPhone	CHAR(10))
	RETURNS		CHAR(14)
AS
BEGIN
	RETURN '(' + SUBSTRING(@cPhone, 1, 3) + ') ' + 
               SUBSTRING(@cPhone, 4, 3) + '-' + 
               SUBSTRING(@cPhone, 7, 4) 
END

Open in new window


Then call it thus:

udfFormatFormatPhone(fieldname)
0
 
LVL 9

Expert Comment

by:sarabhai
ID: 35648376
use of REPLACE function is better.

select replace ('123-45-67','-','')

0
 
LVL 21

Assisted Solution

by:Alpesh Patel
Alpesh Patel earned 332 total points
ID: 35688313
hey you can replace it at SQL code or at Report also. Whatever is good for you.
0
 

Author Closing Comment

by:rustypoot
ID: 35691439
Thanks
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering 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

Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
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.
This Micro Tutorial will teach you how to add a cinematic look to any film or video out there. There are very few simple steps that you will follow to do so. This will be demonstrated using Adobe Premiere Pro CS6.
Enter Foreign and Special Characters Enter characters you can't find on a keyboard using its ASCII code ... and learn how to make a handy reference for yourself using Excel ~ Use these codes in any Windows application! ... whether it is a Micr…

577 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