SQL statement to create generic formatting

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"
zintechAsked:
Who is Participating?
 
jvejskrabCommented:

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
 
Terry WoodsIT GuruCommented:
You would find it much easier to use a programming language to do that eg ASP.NET - would that still achieve your goal?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.