CODEWITHSUNDEEP
sql-serverreplacesql-server-2008-r2
MAK
MAK

Reputation: 7260

Replace like in SQL Server 2008 R2

I have to replace the give string to a specific format.

Example:

Given string is: 

Between 0 and 0:15

Replace into: 

Between '00:00:00' and '00:15:00'

My Attempt:

Declare @tm varchar(max) = 'Between 0 and 0:15'
Declare @rep varchar(max) 

SET @rep = REPLACE(@tm,'0','''00:00:00''');
PRINT(@rep);

Getting result:

Between '00:00:00' and '00:00:00':15

Upvotes: 1

Views: 131

Answers (2)

SubqueryCrunch
SubqueryCrunch

Reputation: 1497

Play with this.

IF OBJECT_ID('fn_FormatTime') IS NOT NULL DROP FUNCTION fn_FormatTime

GO
CREATE FUNCTION fn_FormatTime(@Variable NVARCHAR(100))
RETURNS NVARCHAR(100)
BEGIN
    DECLARE 
        @FirstHour NVARCHAR(100),
        @SecondHour NVARCHAR(100)

    SET @FirstHour = SUBSTRING(@Variable,8,CHARINDEX(' and',@Variable)-8)
    SET @SecondHour = SUBSTRING(@Variable,CHARINDEX('and ',@Variable)+4,LEN(@Variable)-CHARINDEX('and ',@Variable))

    IF @FirstHour = 0 SET @FirstHour = '0:0'
    IF CHARINDEX(':',@FirstHour) = 0 SET @FirstHour = @FirstHour + ':0'

    RETURN 'Between '+CONVERT(NVARCHAR,LEFT(CAST(@FirstHour as time),8))+' and '+CONVERT(NVARCHAR,LEFT(CAST(@SecondHour AS TIME),8))


END
GO

PRINT dbo.fn_FormatTime('Between 0 and 0:15')

Upvotes: 1

Jaques
Jaques

Reputation: 2287

First answer will do it, however I believe that you want to build a SQL string, So my query will include the quotes

Declare @tm varchar(max) = 'Between 0 and 0:15'
Declare @ST varchar(10), @ET varchar(10)
Declare @rep varchar(max) 

--SET @rep = REPLACE(@tm,'0','''00:00:00''');
SET @TM = LTRIM(RTRIM(SubString(@TM, CHARINDEX(' ', @TM), LEN(@TM)))) -- Remove Between
SET @ST = LTRIM(RTRIM(SubString(@TM, 1, CHARINDEX(' ', @TM)))) -- Get Start Time
SET @TM = LTRIM(RTRIM(SubString(@TM, CHARINDEX(' ', @TM) + 4, LEN(@TM)))) -- Remove AND
SET @ET = LTRIM(RTRIM(@TM));


SET @REP = 'Between ''' + Convert(varchar(8), convert(time, case when IsNumeric(@ST) = 1 then REPLACE(@ST,'0','00:00:00') else @ST end)) + 
''' and ''' + Convert(varchar(8), convert(time, case when IsNumeric(@ET) = 1 then REPLACE(@ET,'0','00:00:00') else @ET end)) + ''''

PRINT(@rep);

I showed each step of removing the words, and I didn't assume that it will always be Between or that only the first time can be 0 but the last one too

Upvotes: 2

Related Questions