I have a column test_mac with data type varchar.
For eg following values will be there in that column :
create table test_mac
(
time_in varchar(200)
)
insert into test_mac values ('23:15')
insert into test_mac values ('23:1')
insert into test_mac values ('23:12')
insert into test_mac values ('sdfdsfs')
insert into test_mac values ('23/15')
insert into test_mac values ('2315')
insert into test_mac values ('2:15')
insert into test_mac values ('23:')
I am doing this currently :
select *
from test_mac
where time_in LIKE '%[0-2][0-9]:[0-5][0-59]'
and ISDATE(time_in) = 1
Expected result I require is:
23:15
23:1
23:12
2:15
should be valid times. If there are value as 23:1 I have to update them to 23:10 or for 2:15 to 02:15,
NOTE:
Above is just an creation of test data.Have got to apply some where to just input valid time
If a beautiful solution isn't what you're looking for, here's a slightly long-winded hacky solution I wrote quickly to do the job, it's been lightly tested and works on your sample data:
CREATE TABLE #test_mac
(
time_in VARCHAR(200) ,
formatted VARCHAR(10)
);
INSERT INTO #test_mac
VALUES ( '23:15', NULL ),
( '23:1', NULL ),
( '23:12', NULL ),
( 'sdfdsfs', NULL ),
( '23/15', NULL ),
( '2315', NULL ),
( '2:15', NULL ),
( '23:', NULL );
UPDATE #test_mac
SET formatted = CASE WHEN LEN(SUBSTRING(time_in, 0,
CHARINDEX(':', time_in))) = 2
THEN SUBSTRING(time_in, 0, CHARINDEX(':', time_in))
ELSE '0' + SUBSTRING(time_in, 0,
CHARINDEX(':', time_in))
END + ':'
+ CASE WHEN LEN(SUBSTRING(time_in, CHARINDEX(':', time_in) + 1, 2)) = 2
THEN SUBSTRING(time_in, CHARINDEX(':', time_in) + 1, 2)
ELSE SUBSTRING(time_in, CHARINDEX(':', time_in) + 1, 1) + '0'
END
WHERE time_in LIKE '%[0-9]:[0-5]%' -- looks for a single digit before & after
AND LEN(time_in) <= 5; -- length must be 5 characters or less
SELECT *
FROM #test_mac
WHERE formatted IS NOT NULL;
DROP TABLE #test_mac;