In SQL Server 2000, if I have to create a table to store a very large
number of 10 digit telephone numbers, would I be better off to have a
bigint field or just use a varchar field? If I do a lot of querying
based on the telephone numbers, would it be faster if I use bigint?
Are there any other tricky points that I should know when using bigint
fields? I need to be able to accesss this field from VB6 and VB.NET.
What is the simplest way to format the 10 digit numeric string that represents a phone number, so that the result appearing on a web page looks like (xxx) xxx-xxxx. Should this take place in the database or out at the web server or where?
I am trying to update a bunch of phone numbers in a sql 6.5 db. The phone numbers are in the following formate. (aaa)-xxx-cccc I need to update xxx to yyy. Has anyone done this before. Please help.
Here's my problem: I have to clean up a SQL Server 2005 database with a large number of phone number records (several hundred thousand). The records are of varchar datatype and contain phone numbers in every format imaginable. In fact, many records have written notes regarding the phone numbers after the numbers themselves. What I need to do is format all of the phone numbers to this format:
###-###-####-
Basically I'm figuring I need to do the following: 1. Strip all non-numeric characters from the record 2. Remove the 1 from any records that have a leading 1 (in many cases the records contain stuff like 1-888-555-1234) 3. Remove any digits following the first 10 digits (they don't want to keep any extensions - the formatting is more important) 4. Add dashes after the first three digits, after the second three and at the end of the phone number
This seems like a rather complex problem to me, and honestly I don't even know where to begin. I can accomplish this rather easily in javascript or C#, but writing SQL to solve this is beyond me. I'd really appreciate any help you guys can provide. Thanks alot!
Hello, I have this Access 2K query that I need to re-create in MS SQLServer 2000, so I'm using the Query Analyzer to test it.One of the Access fields stores the home phone number. In the Accessquery, if the phone number is null, it fills it up with zeroes"000000000." If the phone has an input mask, it only gets the 9 numbers(area code included) and if the phone number's good (all numbers) thenit leaves it alone. That Access query is using immediate ifs toaccomplish that task.Does anyone have any idea how to copy this behavior into SQL Server2000? I've using the CASE statement but so far my code is not correct.I get stuck in the input mask. This is the Access code:HomePhone:IIf(IsNull([HomePhone]),"0000000000",IIf(Left([HomePhone],1)="(",Right(Left([Homephone],4),3)& Right(Left([Homephone],9),3) & Right([HomePhone],4),[HomePhone]))Thanks for all your help.JR.
I'm trying to create a case function for home phone ,work phone and cell phone. The thing is some of the home phone numbers either null, zero or less than 10 digits then i'd like to get either cell phone or work phone if they are not null, zero or less than 10 digits.
I'm wondering which way is the best way to store your numeric values. It probably doesnt matter, b/c you can always convert back and forth...but i'm just wondering what the best practice is i guess...
Im currently storing an account id in a sql table. Is there any column data type that would presere the numbers but make it appear as a series of letters and nmumbers when someone looks at the database table?
I have a report with a column which contains either a string such as "N/A" or a number such as 12. A user exports the report to Excel. In Excel the numbers are formatted as text.
I already tried to set the value as CDbl which returns error for the cells containing a string.
The requirement is to export the column to Excel with the numbers formatted as numbers and the strings such as "N/A' in the same column as string.
Why does M$ Query Analyzer display all numbers as positive, no matterwhether they are truly positive or negative ?I am having to cast each column to varchar to find out if there areany negative numbers being hidden from me :(I tried checking Tools/Options/Connections/Use Regional Settings bothon and off, stopping and restarting M$ Query Analyer in betwixt, butno improvement.Am I missing some other option somewhere ?
Hello there,I just want to ask if storing data in dbase is much better than storing it in the file system? Because for one, i am currenlty developing my thesis which uploads a blob.doc file to a web server (currently i'm using the localhost of ASP.NET) then retrieves it from the local hostAlso i want to know if im right at this, the localhost of ASP.NET is the same as the one of a natural web server on the net? Because i'm just thinking of uploading and downloading the files from a web server. Although our thesis defense didn't require us to really upload it on the net, we were advised to use a localhost on our PC's. I'll be just using my local server Is it ok to just use a web server for storing files than a database?
I have a table with a column ID of ContentID. The ID in that column is all NULLs. I need a way to change those nulls to a number. It does not matter what type of number it is as long as they are different. Can someone point me somewhere with a piece of T-SQL that I could use to do that. There are over 24000 rows so cursor change will not be very efficient.
I have an 'ID' column. I'm up to about ID number 40000, but not all are in use, so ID 4354 might not be in any row. I want a list of all numbers which aren't in use. I want to write something like this:
select [numbers from 0 to 40000] where <number> not in (select distinct id from mytable)
SQL Database with column cell-phone formatted 1234567890. I want to display it (123) 456-7890. I use the following to display it as 1234567890: <%# Directory.FieldValue("cell_phone", Container) %> I tried the following code but get a formatting error: <%# Double.Parse(Directory.FieldValue("cell_phone", Container)).ToString("(###) ###-####") %> What stupid error am I making?
I assume a SS# or Phone# should be either a varchar or char field. Butis it a good idea to store a phone# or SS# w/ seperator characters orwithout them?Phone# "(123)-456-7890" or "1234567890"SS# "123-45-6789" or "123456789"I'm not sure which is better. Any opinions? Also has anyone had asituation where a phone# could have an extension? How was it handled?Seperate field or just one?Any input appreciated. Thanks.
Hi Everyone, I have a phone number coming from the database coming in the format of 2132563111. How can I do this in sql query213-563-3111 Please let me know if there is any function that does it. Thanks.
Hello, I have a phone number field with the format (123)-456-7890 I need to convert this to 1234567890 formats while I am retrieving data from the table. How can I do this?
How would I go about setting up a "mask" to automatically place the hyphen in phone numbers in SQL Server? I.e., the user types in an area code and then a hyphen automatically inserts and the cursor is on the next blank space. Thanks for the assist.
How to format phone numbers in sql? in current fields some phone number entered like this: 1 800 7894564 8001237878 1237878 1800blue etc... I am trying to get into uniform like this: 8007834444 Thanks
And I would like to pull them from the table using sql 2008 and have the formatting look like this:
(111)555-1212 x1234.
How can I make this happen? Where numbers are missing, I would prefer to leave the area blank, ie 5551212 becomes 555-1212, and 1115551212 becomes (111)555-1212, extension numbers only where they are currently included.
Hi, I have phone number column in Excel file. The phone number is in this format: 523-349-0212. When this data imported in to SQLServer file, it is not keeping the format, storing it as 5233490212. The data type of this column is varchar. How to keep this format?
i am trying to write a query for phone number lookup . The query should be able to search numbers which have anything matching .... like if the person enters 1918767899 or enters 918767899 the query should be able to find both the records. Itried using the LIKE , but it doesn't work the way it is required.
I am developing a Translator for a mobile phone and I am using MS SQL server 2005 for creating my database.. I am having a problem in creating a relationship between my tables.. I have the following tables: English(engcode,engword) - engcode is primary key Esperanto (espcode, espword) - espcode is promary key EngEsp (engcode,espcode)..
The problem is that the icon "relationship" is disable in sql 2005 and i am nt able 2 create the relationship..