March 12, 2012 at 8:46 am
I've been racking my brain trying to figure this out, and can't seem to come up with a decent solution.
I have a table that includes seven varchar(75) columns for the days of the week (note: I inherited this setup; I did not create it). I'm trying to format my data so that I can bulk upload it to Google business listings.
Problem: the data formatting is inconsistent. (The customer wanted it this way; there's not much I can do about it.) They're free-form fields, so they don't always have the hours listed. (See my sample code if you're not sure what I'm talking about.)
What I want to do is strip anything that is not numeric (with the exception of AM/PM). In the sample data I supplied (below), I don't care about the "call for appointment" or "call such-and-such number for on-call," etc. (those can be blank or null for all I care). Also, in my example, I have "varied" hours for Wednesday; I'm not overly concerned about that, either (I'll probably just put "4:7:00:AM:5:00:PM" or something like that).
If anyone has any ideas as to how to tackle this, I'm all ears. Thanks in advance!
Sample code:
create table #SampleHours (hSun varchar(75), hMon varchar(75), hTue varchar(75), hWed varchar(75), hThu varchar(75), hFri varchar(75), hSat varchar(75))
insert into #SampleHours (hSun, hMon, hTue, hWed, hThu, hFri, hSat) values (null, 'Call for appointment', '7:00 am - 5:00 pm', '7:00 am - 2:00 pm or 5:00 pm (varies)', '7:00 am - 5:00 pm', '7:00 am - 5:00 pm', 'Call 555-1212 for our on-call person')
-- Here's what my sample data looks like . . .
select * from #SampleHours
-- Here's what I've come up with so far.
-- I want to strip anything that's either (1) not a number, (2) not a colon (":"), or (3) not "AM/PM"
select UPPER(REPLACE(REPLACE(REPLACE(ISNULL('1:' + hSun + ',','') + ISNULL('2:' + hMon + ',','') + ISNULL('3:' + hTue + ',','') + ISNULL('4:' + hWed + ',','') + ISNULL('5:' + hThu + ',','') + ISNULL('6:' + hFri + ',','') + ISNULL('7:' + hSat + ',','') , ' - ', ':'), ' ', ':'),'Midnight', '12:00:AM')) as 'Hours' from #SampleHours
drop table #SampleHours
+--------------------------------------------------------------------------------------+
Check out my blog at https://pianorayk.wordpress.com/
March 12, 2012 at 8:53 am
ray here is an older Scalar function I have saved;
If I were doing it today, I'd convert it to an inline tableValue Function, but this would be quick;
you could also use a regualr expression on the applicaiotion side:
CREATE FUNCTION StripNonNumeric(@OriginalText VARCHAR(8000))
RETURNS VARCHAR(8000)
BEGIN
DECLARE @CleanedText VARCHAR(8000)
;WITH tally (N) as
(SELECT TOP 10000 row_number() OVER (ORDER BY sc1.id)
FROM Master.dbo.SysColumns sc1
CROSS JOIN Master.dbo.SysColumns sc2)
SELECT @CleanedText = ISNULL(@CleanedText,'') +
CASE
--ascii numbers are 48(for '0') thru 57 (for '9')
WHEN ASCII(SUBSTRING(@OriginalText,Tally.N,1)) BETWEEN 48 AND 57 THEN SUBSTRING(@OriginalText,Tally.N,1) ELSE '' END
FROM tally WHERE Tally.N <= LEN(@OriginalText)
RETURN @CleanedText
END
'VB function:
Friend Function StripNonNumeric(ByVal s As String) As String
Return System.Text.RegularExpressions.Regex.Replace(s, "[^\d]", String.Empty)
End Function
Lowell
March 12, 2012 at 9:05 am
Ah yes, more tally tables. I was going to try that for another question I had, but I got sidetracked and never got back to it.
I'll take a look and let you know how I make out.
Thanks for your help as always!
+--------------------------------------------------------------------------------------+
Check out my blog at https://pianorayk.wordpress.com/
March 12, 2012 at 9:43 am
Wow -- that almost works too well! 🙂
I do need to tweak it to ignore the colon characters or AM/PM as well, but it does give me something to work with (which is more than what I had before)!
Thanks again!
+--------------------------------------------------------------------------------------+
Check out my blog at https://pianorayk.wordpress.com/
March 12, 2012 at 9:57 am
Replace 'am' and 'pm' with alternate unused char, ie char(0) and char(1) and reverting back afterwards may help with the char removal with tally table
Far away is close at hand in the images of elsewhere.
Anon.
March 12, 2012 at 10:10 am
this would ignore colons and am/pm, i think...but it doesn't leave whitespace between a time and am/pm...
SELECT dbo.StripNonNumeric('Wednesday, 12:14 pm')
returns "12:14pm",, is that what you want?
you could get fancier on the checking of preceeding digints, and allow spaces as well....depends now on the desired output.
ALTER FUNCTION StripNonNumeric(@OriginalText VARCHAR(8000))
RETURNS VARCHAR(8000)
BEGIN
DECLARE @CleanedText VARCHAR(8000)
;WITH tally (N) as
(SELECT TOP 10000 row_number() OVER (ORDER BY sc1.id)
FROM Master.dbo.SysColumns sc1
CROSS JOIN Master.dbo.SysColumns sc2)
SELECT @CleanedText = ISNULL(@CleanedText,'') +
CASE
--ascii numbers are 48(for '0') thru 57 (for '9')
WHEN ASCII(SUBSTRING(@OriginalText,Tally.N,1)) BETWEEN 48 AND 57
THEN SUBSTRING(@OriginalText,Tally.N,1)
--SELECT ASCII(':') = 58
WHEN ASCII(SUBSTRING(@OriginalText,Tally.N,1)) = 58
THEN SUBSTRING(@OriginalText,Tally.N,1)
--SELECT ASCII('p') 'AM Or PM
WHEN ASCII(SUBSTRING(@OriginalText,Tally.N,1))IN(65,97,80,112) --A,a,P,p
AND ASCII(SUBSTRING(@OriginalText,Tally.N + 1 ,1))IN(77,109) --M,m THEN SUBSTRING(@OriginalText,Tally.N,1)
THEN SUBSTRING(@OriginalText,Tally.N,1)
--if this was the M, and the preceeding was A/P
WHEN ASCII(SUBSTRING(@OriginalText,Tally.N,1))IN(77,109) --M,m
AND ASCII(SUBSTRING(@OriginalText,Tally.N - 1 ,1)) IN(65,97,80,112) --A,a,P,p THEN SUBSTRING(@OriginalText,Tally.N,1)
THEN SUBSTRING(@OriginalText,Tally.N,1)
ELSE '' END
FROM tally WHERE Tally.N <= LEN(@OriginalText)
RETURN @CleanedText
END
GO
Lowell
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply