Convert alphanumeric phone numbers to Numeric

by Amol 17. February 2011 18:59

Most of the phone numbers are represented in alphanumeric format to remember easily. For example, 1-800-MSFT .  You can see many such numbers in USA. Here I am writing T-SQL to convert alphanumeric numbers to real numbers. On the phone keypad we can see a to z characters to represent its equivalent numeric number.  For example, number 2 represent a, b and c.  A variable @NumberFormat holds all the numbers in sequence of characters.  'abcdefghijklmnopqrstuvwxyz' = '22233344455566677778889999' 

 

-- script to convert alphanumeric phone numbers to numeric

DECLARE @PhoneNumber VARCHAR(32) = '1-800-MSFT'

 

DECLARE @PhoneLength TINYINT = LEN(@PhoneNumber)

DECLARE @Id TINYINT = 1,

        @NumberFormat VARCHAR(26) = '22233344455566677778889999',

        @NumericNumber VARCHAR(32);

 

WITH ConvertToNumber AS

(

      SELECT SUBSTRING(@PhoneNumber, @Id, 1) AS [Numbers],

             @Id + 1 AS Id

      UNION ALL

      SELECT SUBSTRING(@PhoneNumber, Id, 1) AS [Numbers],

             Id + 1 AS Id

      FROM ConvertToNumber

      WHERE Id <= @PhoneLength

)

SELECT @NumericNumber =

       COALESCE(@NumericNumber + '', '') +

       CASE WHEN ASCII([Numbers]) BETWEEN 65 AND 90

THEN SUBSTRING(@NumberFormat, (ASCII([Numbers])-64), 1)

            ELSE [Numbers]

       END

FROM ConvertToNumber

SELECT @NumericNumber AS PhoneNumber

 

PhoneNumber

--------------------------------

1-800-6738


 

Tags:

SQL Server Scripts

T-SQL to find number of string occurrences with in a string

by Amol 16. February 2011 19:01

In SQL Server, there is no inbuilt function which counts number of string occurrences within a given string. To find this, we will have to use trick here.  First replace the search string with search string by appending one space to it and get the length of the string. And subtract the new length from original string length

Suppose we want to find string “O” from the string “MICROSOFT”, So replace “O” with “O “ (+ space), your string will becomes “MICRO SO FT”.

Here is the scalar function to this.

CREATE FUNCTION [Asset].[Occurrences]

(

@Expression NVARCHAR (MAX),

@SearchString NVARCHAR (256)

)

RETURNS INT

AS

BEGIN

 

    RETURN(

            LEN(REPLACE(

                         @Expression

                        ,@SearchString

                        ,@SearchString + ' ')

                       )

            - LEN(@Expression)

          )

 

END

Tags:

SQL Server Scripts

Grouping Sets in SQL Server 2008

by Amol 15. February 2011 19:31

 

IF OBJECT_ID ('[dbo].[Wrestler]') IS NOT NULL
DROP TABLE [dbo].[Wrestler]
GO 

CREATE TABLE [dbo].[Wrestler]
(
ID INT IDENTITY(1,1) NOT NULL,
Name VARCHAR(255) NOT NULL,
Country VARCHAR(50) NOT NULL,
[State] VARCHAR(50)
)

GO

INSERT INTO [dbo].[Wrestler]
SELECT 'John Cena', 'USA', 'West Newbury' UNION ALL
SELECT 'Batista', 'USA', 'Washington DC' UNION ALL
SELECT 'Ray Mysterio', 'USA', 'California' UNION ALL
SELECT 'Garcia Twins', 'USA', 'California' UNION ALL
SELECT 'Great Khali', 'INDIA', 'Punjab' UNION ALL
SELECT 'Undertaker', 'USA', 'TEXAS' 

GO

SELECT Name, COUNT(*) AS [Count]
FROM [dbo].[Wrestler]
GROUP BY Name 

UNION ALL

SELECT Country, COUNT(*) AS [Count]
FROM [dbo].[Wrestler]
GROUP BY Country 

UNION ALL

SELECT [State], COUNT(*) AS [Count]
FROM [dbo].[Wrestler]
GROUP BY [State] 

GO

SELECT Name, [State], Country, Count(*) AS [Count]
FROM [dbo].[Wrestler]
GROUP BY GROUPING SETS
(
Name,
[State],
Country,
()
)
ORDER BY Name, [State], Country


Tags:

SQL Server Scripts

Category

Recent Posts

Tag cloud