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

x
?
Solved

SQL statement to create generic formatting

Posted on 2011-10-19
2
Medium Priority
?
262 Views
Last Modified: 2012-06-27
What I am seeking to do is find a SQL statement that converts a field to a standardized format.  For example: I would like the values "01.01.01" and "1.1.1" and "1-1-1" and "01-01-01" to all be formatted as "01-01-01".  I already am running a SQL statement that changes all of the period "." characters into hyphen "-" characters, so all I need to know how to do is format the numbers correctly.  if there is a number such as "10-1-1" or "1-10-1" the "10" should stay as is and be converted to "01-10-01" and "01-10-01"
0
Comment
Question by:zintech
2 Comments
 
LVL 35

Expert Comment

by:Terry Woods
ID: 36996001
You would find it much easier to use a programming language to do that eg ASP.NET - would that still achieve your goal?
0
 
LVL 3

Accepted Solution

by:
jvejskrab earned 2000 total points
ID: 36998515

possible solution:

CREATE TABLE #temp (
      string varchar(8)
)

INSERT INTO #temp VALUES ('01.01.01')
INSERT INTO #temp VALUES ('1.10.1')
INSERT INTO #temp VALUES ('1-1-1')
INSERT INTO #temp VALUES ('01.1.10')
INSERT INTO #temp VALUES ('10-1-01')


SELECT RIGHT('0'+REPLACE(LEFT(r, 2), '-', ''), 2)+'-'+RIGHT('0'+REPLACE(SUBSTRING(r, CHARINDEX('-', r) + 1, 2),'-',''), 2)+'-'+RIGHT('0'+REPLACE(RIGHT(r, 2), '-', ''), 2)
FROM ( SELECT REPLACE(string, '.', '-') r
            FROM #temp ) a

DROP TABLE #temp


GO

Or you can write some function


CREATE FUNCTION dbo.FCorrectFormat (@string varchar(8))
RETURNS varchar(8)
AS
BEGIN
      
      SET @string = REPLACE(@string, '.', '-')
      
      SET @string = RIGHT('0'+REPLACE(LEFT(@string, 2), '-', ''), 2)+'-'+RIGHT('0'+REPLACE(SUBSTRING(@string, CHARINDEX('-', @string) + 1, 2),'-',''), 2)+'-'+RIGHT('0'+REPLACE(RIGHT(@string, 2), '-', ''), 2)
      
      RETURN @string
      
END
GO


CREATE TABLE #temp (
      string varchar(8)
)

INSERT INTO #temp VALUES ('01.01.01')
INSERT INTO #temp VALUES ('1.10.1')
INSERT INTO #temp VALUES ('1-1-1')
INSERT INTO #temp VALUES ('01.1.10')
INSERT INTO #temp VALUES ('10-1-01')

SELECT dbo.FCorrectFormat(string) FROM #temp


DROP TABLE #temp
DROP FUNCTION dbo.FCorrectFormat
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

The article shows the basic steps of integrating an HTML theme template into an ASP.NET MVC project
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Suggested Courses

572 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