تبلیغات
یادداشتهای علی سالخورده حقیقی - Format Phone Number function
 
یادداشتهای علی سالخورده حقیقی
We Provide Solutions for The Future Developement.
درباره وبلاگ


دریافت لوح تقدیر از سازمان ملل به عنوان مشاور فناوری اطلاعات سازمان در انتخابات برون مرزی سال 2004.

مدیر وبلاگ : علی سالخورده
نویسندگان
نظرسنجی
از کدامیک از آنتی ویروس های زیر استفاده میکنید و راضی هستید؟








آمار وبلاگ
  • کل بازدید :
  • بازدید امروز :
  • بازدید دیروز :
  • بازدید این ماه :
  • بازدید ماه قبل :
  • تعداد نویسندگان :
  • تعداد کل پست ها :
  • آخرین بازدید :
  • آخرین بروز رسانی :

This function was written according to my needs , please modify to suit your requirements.

 

Input : VARCHAR(8000)

Output : VARCHAR(8000) [Formatted Phone Number]

 

Ex :

'8601234567' returns '(860)123-4567'

 

 

Tested on : SQL 2000,2005,2008

CREATE FUNCTION [dbo].[FormatPhoneNumber](@in VARCHAR(8000))
RETURNS VARCHAR(8000)
AS
  BEGIN
      DECLARE @out AS VARCHAR(8000)
      DECLARE @counter AS INT
      DECLARE @outPos AS INT
      DECLARE @currentChar AS VARCHAR(1)

      SET @counter=1

      SELECT @out = ''

      WHILE @counter <= len(@in)
        BEGIN
            SET @currentChar=substring(@in, @counter, 1)

            IF ( isnumeric(@currentChar) = 1
                 AND @currentChar != '+'
                 AND @currentChar != '-'
                 AND @currentChar != '.' )
              BEGIN
                  IF ( ( len(@out) > 0 )
                        OR ( len(@out) = 0
                             AND @currentChar != '0' ) )
                    BEGIN
                        SET @out= @out + @currentChar;
                    END
              END

            SET @counter=@counter + 1
        END

      IF ( len(@out) = 10 )
        BEGIN
            SELECT @in = @out;

            SELECT @out = ''

            SET @counter=1

            WHILE @counter <= len(@in)
              BEGIN
                  SET @currentChar=substring(@in, @counter, 1)

                  BEGIN
                      IF ( @counter = 1 )
                        BEGIN
                            SET @out= '(';
                        END

                      IF ( @counter = 4 )
                        BEGIN
                            SET @out= @out + ')';
                        END

                      IF ( @counter = 7 )
                        BEGIN
                            SET @out= @out + '-';
                        END

                      BEGIN
                          SET @out= @out + @currentChar;
                      END
                  END

                  SET @counter=@counter + 1
              END
        END

      RETURN @out
  END





نوع مطلب : پایگاه داده SQL، نکته ها و ترفندها، 
برچسب ها :
لینک های مرتبط :

       نظرات
1389/09/19
علی سالخورده
1396/10/6 17:44
http://popcorntimewindows.strikingly.com
1396/05/16 21:51
Hello, I enjoy reading all of your article. I wanted to write
a little comment to support you.
1396/05/8 11:54
I used to be recommended this blog via my cousin. I am no longer positive whether this submit is written by means of him as nobody else recognise such
distinctive about my problem. You're amazing! Thanks!
1396/05/6 23:25
Great article! That is the type of information that are supposed to be shared across the internet.
Disgrace on Google for no longer positioning this publish higher!
Come on over and discuss with my web site .
Thanks =)
1396/04/31 16:13
Hey I know this is off topic but I was wondering if you knew of any
widgets I could add to my blog that automatically tweet my newest twitter
updates. I've been looking for a plug-in like this for quite some time and was hoping
maybe you would have some experience with something like this.
Please let me know if you run into anything. I truly enjoy reading your blog
and I look forward to your new updates.
1396/04/11 11:32
Hi there very nice website!! Guy .. Excellent .. Wonderful ..
I will bookmark your site and take the feeds
also? I am satisfied to search out so many useful information here within the put up, we want develop
more techniques on this regard, thank you for sharing.
. . . . .
1396/02/14 00:46
Do you mind if I quote a few of your posts as long as I provide credit and sources back
to your site? My website is in the very same niche as
yours and my users would truly benefit from some of the information you present here.
Please let me know if this ok with you. Thanks a lot!
1396/02/13 06:16
Hurrah, that's what I was seeking for, what a information! existing here at this webpage, thanks admin of this web site.
1396/01/25 05:47
Hiya very cool web site!! Man .. Beautiful .. Wonderful ..
I will bookmark your website and take the feeds
also? I am satisfied to find so many useful info here in the submit,
we need work out more strategies in this regard, thanks for sharing.
. . . . .
1396/01/25 04:34
Wow, awesome blog layout! How long have you been blogging for?
you made blogging look easy. The overall look of your website is
excellent, as well as the content!
1396/01/23 01:05
Just want to say your article is as amazing. The clarity in your post is just spectacular and i
could assume you're an expert on this subject. Fine with your permission allow me to
grab your feed to keep up to date with forthcoming post.
Thanks a million and please continue the enjoyable work.
1396/01/22 16:52
I know this web page presents quality depending posts and additional material, is
there any other site which provides such data in quality?
1396/01/22 15:40
certainly like your web-site but you need to take a look at the spelling on several of your posts.
Many of them are rife with spelling problems and I find it very troublesome to tell the truth then again I'll
definitely come again again.
1396/01/16 07:57
Awesome post.
 
لبخندناراحتچشمک
نیشخندبغلسوال
قلبخجالتزبان
ماچتعجبعصبانی
عینکشیطانگریه
خندهقهقههخداحافظ
سبزقهرهورا
دستگلتفکر