April 22, 2009 at 12:52 pm
Bob Hovious (4/22/2009)
Hey Guru:I like your Lobo-esque avatar too. I can't remember the exact site where I got this one (which is currently an angled perspective in blue on a black background). Check back in a few hours and it may change.
I use Google's image search to find biohazard emblems. There are some public sites like photobucket where people post up images. It was probably from one of those.
I like using biohazard symbols because so many have been created that I can switch them around from time to time to suit my mood but still have it be recognizably "my" logo.
Bob
Cool. Do you have a higher res version of it that wouldn't mind sharing? I cycle trhu images for my desktop and that would look great.
The Lobo image is a scan of a drawing I did of Lobo probably more then 15 years back. I had initially planned to go into comic book art when I was a teen but stuf got in the way (women & cars) and work became the priority and then the personal computer came...
Thanks
Kindest Regards,
Just say No to Facebook!April 22, 2009 at 12:59 pm
No problem. Here's what I've got. My understanding is that it can't be used for commercial purposes.
Jeff: Who did your awesomely animated NO RBAR logo?
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
April 22, 2009 at 2:01 pm
Bob Hovious (4/22/2009)
No problem. Here's what I've got. My understanding is that it can't be used for commercial purposes.Jeff: Who did your awesomely animated NO RBAR logo?
Thanks Bob!
Kindest Regards,
Just say No to Facebook!April 22, 2009 at 2:05 pm
Bob Hovious (4/22/2009)
No problem. Here's what I've got. My understanding is that it can't be used for commercial purposes.Jeff: Who did your awesomely animated NO RBAR logo?
I found a clip with some guy headbutting the "Death Star". Overlaying RBAR and squishing it in in the last 3 frames that it appears was easy to do using Corel Photo Paint.
--Jeff Moden
Change is inevitable... Change for the better is not.
April 22, 2009 at 2:12 pm
YSLGuru (4/22/2009)
Replies like this are exactly the reason why SQLServerCentral.com pays you the BIG bucks! This is a great answer and I would implement it if it weren't for the fact that doing so risks breakiing/bending our support with the vendors who's paccounting software uses the DB.Thanks for sharing though because even though I can't do this in the DB this is a really good suggestion and I hope others make note of it to as I'm sure at least 1 other person out there could make use of this suggestion.
Heh... thanks. There's another way to do it, as well... "Sister" table. That is, make a parallel table with the PK, the phone number, and the formatted persisted calculated column. A trigger on the original table and the phone number column probably won't break anyone's code.
Another way that I cheat 3rd party code is to rename the original table and create a SELECT * pass-through view using the original table name. That keeps the vendor code happy. Then, I can do just about anything I want to the underlying table. Of course, always try that type of stuff on a test system first, but it normally works pretty well. Underlying indexes from the view to the table continue to work just fine, to boot.
--Jeff Moden
Change is inevitable... Change for the better is not.
April 22, 2009 at 2:25 pm
You're an evil man, Jeff Moden.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
July 17, 2009 at 9:36 pm
Hi. Since you are talking about phone number formatting, I would like to add a problem that we have at work. Writing a query is not an option. The report we need to generate runs off a stored procedure, and we do not want to touch it. What I am looking for is a way to correctly format a phone number at runtime.
The source of the number does not control user input. Therefore the user could put in various characters either by accident or because he/she is used to a particular way of presenting phone numbers. So, the problem is twofold.
1. I need to strip the phone number of any non-numeric characters.
2. Present the phone number as (123) 456-7890
On the number 2, my solution is
="(" & Mid("1234567890",1,3) & ") " & Mid("1234567890",4,3) & "-" & Mid("1234567890",7,4)
I am left with the problem of how to strip the number of all non-numeric characters.
Any ideas would be greatly appreciated.
Thank you.
July 17, 2009 at 10:51 pm
tsmith (7/17/2009)
Hi. Since you are talking about phone number formatting, I would like to add a problem that we have at work. Writing a query is not an option. The report we need to generate runs off a stored procedure, and we do not want to touch it. What I am looking for is a way to correctly format a phone number at runtime.The source of the number does not control user input. Therefore the user could put in various characters either by accident or because he/she is used to a particular way of presenting phone numbers. So, the problem is twofold.
1. I need to strip the phone number of any non-numeric characters.
2. Present the phone number as (123) 456-7890
On the number 2, my solution is
="(" & Mid("1234567890",1,3) & ") " & Mid("1234567890",4,3) & "-" & Mid("1234567890",7,4)
I am left with the problem of how to strip the number of all non-numeric characters.
Any ideas would be greatly appreciated.
Thank you.
I wouldn't store any formatting like this in the database. In fact, if I were designing the GUI, I'd have separate field for the NPA, NXX, and Line of all phone numbers and would be stored that way in the DB so they could be easily validated without having to do a substring. But, whatever. Here's a function that will do both Item 1 and 2 that you asked for and will provide at least a length check...
CREATE FUNCTION dbo.FormatPhone
(
@String VARCHAR(8000)
)
RETURNS CHAR(14)
AS
BEGIN
--===== Declare and preset local variables
DECLARE @Len INT
SELECT @Len = LEN(@String)+1
--===== Remove non-digit characters
SELECT @String = STUFF(@String,@Len-t.N,1,'')
FROM dbo.Tally t
WHERE t.N < @Len
AND SUBSTRING(@String,@Len-t.N,1) LIKE '[^0-9]'
--===== Return the cleaned value and exit
RETURN CASE
WHEN LEN(@String) = 10
THEN '('+STUFF(STUFF(@String,7,0,'-'),4,0,') ')
ELSE 'Bad Entry'
END
END
Here's a test callout for the function...
SELECT dbo.FormatPhone( 'j&1gd2@uf*(3456-+*&^%$AQ7GDT8HWT9&^%&&*F&ST&TF*%F0*ED*F^S*')
--Jeff Moden
Change is inevitable... Change for the better is not.
July 17, 2009 at 10:53 pm
Almost forgot... if you don't know what a Tally table is or how it replaces a loop, please see the following article...
http://www.sqlservercentral.com/articles/T-SQL/62867/
--Jeff Moden
Change is inevitable... Change for the better is not.
July 18, 2009 at 2:24 am
Thank you for your answer. I will try it on Monday. I'm sorry for the double post, but after I asked about it here, I thought maybe I should ask about it separately. But then I tried twice to delete my post from here and it wouldn't.
Again , thank you very much for your response.
July 18, 2009 at 7:02 am
tsmith (7/18/2009)
Thank you for your answer. I will try it on Monday. I'm sorry for the double post, but after I asked about it here, I thought maybe I should ask about it separately. But then I tried twice to delete my post from here and it wouldn't.Again , thank you very much for your response.
Thanks for the feedback. 🙂
They had a problem with folks deleting their follow up posts once they got an answer so they turned that feature off. Understood on the double post... people get a bit anxious when they need help and I can't say I blame them.
--Jeff Moden
Change is inevitable... Change for the better is not.
July 19, 2009 at 9:09 pm
tsmith (7/17/2009)
Writing a query is not an option. The report we need to generate runs off a stored procedure, and we do not want to touch it. What I am looking for is a way to correctly format a phone number at runtime.
If your reporting platform is in fact SSRS, you've got the flexibility of VB code to manipulate strings in the report definition. Step 1, strip out non-numeric characters using the IsNumeric() function - you can probably find some prefab functions out there such as this (that's simply the first result I get in Google). Step 2: use the Format() function on the numeric output from Step 1, something like Format(lngUserPhoneNumber, "(000) 000-0000"). You have the option of putting one or both of these steps in the report's custom Code, where you might also want to put in validation such as checking that the user entered the right number of numeric characters to be a valid phone number.
September 28, 2010 at 2:43 pm
SELECT T.sPhoneNum1 AS 'Phone Number - Raw/Unformatted',
'(' + Stuff(Stuff(T.sPhoneNum1,len(T.sPhoneNum1)-3,0,'-'),len(T.sPhoneNum1)-6,0,') ') AS 'Phone Number-Formatted'
FROM (SELECT '9991112222' AS 'sPhoneNum1') T
Will work for phone number with or without Area code
September 28, 2010 at 2:51 pm
Rakesh Sharma-471339 (9/28/2010)
SELECT T.sPhoneNum1 AS 'Phone Number - Raw/Unformatted','(' + Stuff(Stuff(T.sPhoneNum1,len(T.sPhoneNum1)-3,0,'-'),len(T.sPhoneNum1)-6,0,') ') AS 'Phone Number-Formatted'
FROM (SELECT '9991112222' AS 'sPhoneNum1') T
Will work for phone number with or without Area code
Did you happen to see that the previous response on this thread was more than a year old? :w00t:
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
April 1, 2016 at 6:48 am
The Dixie Flatline (4/17/2009)
if you just stored phone numbers as integers, you would trim fat out of the row size as well.
… and if you stored UK phone numbers as INTEGER, you'd need to prepend each one with '0' before delivering it to a UI object or similar to format it, because all UK phone numbers begin with a zero.
This means that when you export and display phone numbers in a spreadsheet app such as Excel, you first have to format that column's cells as Text, or hilarity ensues when said app 'decides' that the column is numeric >sigh<. I daresay there are (?) many other countries where some/all phone numbers begin with zero.
Having said that, us UK-ites do envy(-ish) the fact that all US phone numbers have ten digits AND use the identical 3-3-4 format for display. Over here, we (properly, anyway) use one of three different display formats (3-4-4, 4-3-4, 5-6) depending on the first four digits in the number.
Oh, and just for extra s*its and giggles, a UK phone number can validly contain eleven or ten digits in it, again depending on the first few digits: which when formatted for display purposes, become a character string of either twelve or thirteen characters.
What larks, eh?!! :crazy:
Viewing 15 posts - 16 through 30 (of 32 total)
You must be logged in to reply to this topic. Login to reply