T-SQL - To convert any format phone number into Standard US format

CREATE FUNCTION [dbo].[fnStandardPhone]
   (
     @PhoneNumber VARCHAR(32)
   )
RETURNS VARCHAR(32)
AS 
   BEGIN

       DECLARE @Phone CHAR(32)

   

       SET @Phone = @PhoneNumber

   

   -- cleanse phone number string

   WHILE PATINDEX('%[^0-9]%', @PhoneNumber) > 0 
           SET @PhoneNumber = REPLACE(@PhoneNumber,
                                      SUBSTRING(@PhoneNumber,
                                                PATINDEX('%[^0-9]%',
                                                         @PhoneNumber), 1),
                                      '')

   

   -- skip foreign phones

       IF ( SUBSTRING(@PhoneNumber, 1, 1) = '1'
            OR SUBSTRING(@PhoneNumber, 1, 1) = '+'
            OR SUBSTRING(@PhoneNumber, 1, 1) = '0'
          )
           AND LEN(@PhoneNumber) > 11 
           RETURN @Phone


     -- build US standard phone number

       SET @Phone = @PhoneNumber

   

       SET @PhoneNumber = SUBSTRING(@PhoneNumber, 1, 3) + '-'
           + +SUBSTRING(@PhoneNumber, 4, 3) + '-' + SUBSTRING(@PhoneNumber, 7,
                                                             4)

   

       IF LEN(@Phone) - 10 > 1 
           SET @PhoneNumber = @PhoneNumber + ' x' + SUBSTRING(@Phone, 11,
                                                             LEN(@Phone) - 10)

   

       RETURN @PhoneNumber

   END


GO

TSQL - To visually identify if there is a Carriage Return / New Line character within the VARCHAR column

Scenario:

We have a scenario, where in we need to know if there is any carriage return('\r') or a new line('\n') character is embedded in a VARCHAR/TEXT column. 

Solution: 

This cannot be simply achieved by using SELECT statement. PRINT function enables us to know the presence of such characters. Let us now see how.

Step 1:

 Let me a create simple table for your easy understanding using the following script. 

CREATE TABLE [dbo].[TestDB_Test1](
[COL1] [int] IDENTITY(1,1) NOT NULL,
[COL2] [varchar](150) NULL
) ON [PRIMARY]

GO

Step 2: 

Lets insert two rows for COL2, one  without carriage return/new line  and the other one with the carriage return/new line. 

-- Sample data without carriage return/ new line character

INSERT INTO [dbo].[TestDB_Test1]

           ([COL2])

     VALUES

           ('I am Line1. I am Line2')

GO

-- Sample data with carriage return/new line character

INSERT INTO [dbo].[TestDB_Test1]

            ([COL2])

VALUES

('I am Line1.' + CHAR(10)+ 'I am Line2')

Step 3: 

Run a SELECT query, to see how the data appears. 

SELECT * from dbo.TESTDB_Test1 

The two rows appears to be same after a SELECT query. 

Step 4: 

Create a variable to hold the value of COL2 using a select query. 

DECLARE @var varchar(150) 

SELECT @var = col2 FROM TestDB_Test1 where col1 = 1

PRINT @var

DECLARE @var varchar(150) 

SELECT @var = col2 FROM TestDB_Test1 where col1 = 2

PRINT @var

This clearly shows that the text in row 2 had a new line character in between.  In order to remove such redundant characters, one could always use REPLACE function.