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.
Thanks in advance
Ken
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?
View 1 Replies View RelatedHere'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!
Hi,In SQL Server 2000, if I have to create a table to store a very largenumber of 10 digit telephone numbers, would I be better off to have abigint field or just use a varchar field? If I do a lot of queryingbased on the telephone numbers, would it be faster if I use bigint?Are there any other tricky points that I should know when using bigintfields? I need to be able to accesss this field from VB6 and VB.NET.Thanks,--TP
View 3 Replies View Related I am trying to find all the records in our database that have the incorrect phone number format and fix them to the correct format.
CREATE TABLE MDR (
SiteName nvarchar(255),
BusinessEmailAddress nvarchar(255),
FirstName nvarchar(255),
LastName nvarchar(255),
JobTitle nvarchar(255),
PersonBusinessPhoneNumber nvarchar(255),
SiteBusinessPhoneNumber nvarchar(255))
[code]....
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.
View 2 Replies View Related
Hi,
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'd appreciate it if you could help me on this?
Thanks in advance.
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...
thx
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.
Thanks for any help
I have a table with a field called "host" and I want to replace the value of this column in each row with 'Host' followed by a random number. I have created the below function to do this:
DECLARE @Random varchar;
SELECT @Random = CONVERT(int, (20+1)*RAND())+ 1;
UPDATE AnalogLines Set [Host Name] = 'Host' + @Random
This sorta works... the problem is, the random number is determine and then the SAME random number is applied to each row. I need a different random number for every row. Any ideas?
Large design flaw, I know, but we have a table with a varchar field which contains alpha - numeric values. I need to change just the numerics, which can be in an position on the field:
Sample data of the field:
Rec1: Sally Morgan 201-555-1212
Rec2: 555-4040 John Smith
Rec3: Jane Houstin 201-555-6452 ext1223
Desired result:
Rec1: Sally Morgan 999-999-9999
Rec2: 999-9999 John Smith
Rec3: Jane Houstin 999-999-9999 ext9999
There's a bunch of UDFs out there for selecting just the numerics, but I'm having trouble throwing it into a viable script for repeatable execution.
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.
Any suggestions?
I have two tables. One table has empty column (ID) and I want to generate serial numbers in that column based two tables columns(LoanNum) condition.
Table A
ID LoanNum
Null 1234
Null 2345
Null 3456
Null 4567
Null 5678
Null 6789
Table B
LoanNum
1234
2345
3456
4567
5678
6789
2324
4352
4235
Id is not primary key, but should not duplicates keys, it is just a column want to generate serial number (1,2,3,4...etc) How to generate?
Here is my problem. I have a table with 4 columns id1,id2,id3,boxnum. Here is some sample data.
id1 id2 id3 boxnum
1 1 1
1 1 1
1 2 1
1 2 1
1 2 1
2 2 2
2 3 4
What I need is to be able to update boxnum with sequential numbers based on the unique set of values from the id columns. So my output would be this.
id1 id2 id3 boxnum
1 1 1 1
1 1 1 2
1 2 1 1
1 2 1 2
1 2 1 3
2 2 2 1
2 3 4 1
If Exists ( Select c.name from sys.columns c where object_id = object_id('HH835HP') and C.name = 'ID_1' )
Begin
UPDATE HH835HP
SET ID_1 =
( select ROW_NUMBER() OVER(ORDER BY CHKDTS ASC) AS ID_1 FROM HH835HP ) ;
End;
Obviously... The stuff inside the IF is wrong syntax...I mean
UPDATE HH835HP
SET ID_1 =
( select ROW_NUMBER() OVER(ORDER BY CHKDTS ASC) AS ID_1 FROM HH835HP ) ;
I have a below table,
DECLARE @TBL TABLE (ItemId INT IDENTITY(1,1), ItemName NVARCHAR(20), ParentItemName NVARCHAR(20), ItemOrder INT, ReportId INT)
INSERT INTO @TBL (ItemName, ParentItemName, ItemOrder, ReportId)
VALUES('Item1', NULL, 1, 5),('Item1-Child1', 'Item1', 0, 5),('Item1-Child2', 'Item1', 0, 5),('Item2', NULL, 2, 5),
('Item11', NULL, 1, 6),('Item12', NULL, 2, 6),('Item12-Child1', 'Item12', 0, 6),('Item13', NULL, 3, 6)
SELECT * FROM @TBL
Here,
1. for all ReportId, child items's ItemOrder = 0
2. example, for ReportId = 5, both child items ("Item1-Child1" & "Item1-Child1") of parent "Item1" has ItemOrder = 0
I need to,
1. update all child items with ascending numbers starts with 1 against each parent and each report.
2. for each different parent or different report, order by should starts with 1 again.
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 ?
View 7 Replies View RelatedI 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)
but don't know how. Any clues?
I'm trying to write data to excel from an ssis component to a excel destination.
Even thought I'm writing numerics, every cell gets this error with a green tag:
Convert numbers stored as text to numbers
Excel Cells were all pre-formated to accounting 2 decimal, and if i manually type the exact data Im sending it formats just fine.
I'm hearing this a common problem -
On another project I was able to find a workaround for the web based version of excel, by writing this to the top of the file:
<style>.text { mso-number-format:@; } </style>
is there anything I can pre-set in excel (cells are already formated) or write to my file so that numerics are seen as numerics and not text.
Maybe some setting in my write drivers - using sql servers excel destination.
So close.. Thanks for any help or information.
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 have 2 tables with different phone field format. Looks like below:
TableA
field name area
record 111
and field name phone
record 2223333
TableB
field name Phone
record (111) 222-3333 *
*with a space between (111) and 222
I want to transfer tableA (area+phone) field to tableB phone field and transfer tableB phone field to tableA area field and phone field.
Anybody can help me to finish it? Thank you very much!!!
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.
View 6 Replies View RelatedIn my report I am combing 2 fields as
=Fields!PROPERTY.Value & " - " & Fields!PHONE.Value
this results in PropertyName - 5555551212
How would I add in formatting for phone so my resulting display is (555) 555-1212?
hai,
how can i send sms through web page(without using web service) to mobile phone using SQL server 2005
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.
Hi all,
i have a table in SQL with +- 5000 phone numbers in various styles
e.g.
0515 - 578989
033 2981861
+31 (0)30 602 44 44
03.28.68.63.18
03/658.85.80
I want this to convert to international format like this
003237778899
0031306024444
Do i have to write 10 replace after each other or is there an easier way to format the phone numbers?
Thx in advance!!
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.
Mark
Somewhere down here in Texas
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
I have a table filled with phone numbers entered in every different way possible
(5551212, 1115551212, 111-555-1212, 111 555 1212, 111-555-1212 ex 1234, 5551212 x1234)
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 a string text box '1234567890' and I'm trying to format it as phone number 123-456-7890 in reporting services. But I couldn't figure it out.
Can someone help me?
If I have a string of 10 numbers (with no other characters), what is the best way to format them into a standard phone format: xxx-xxx-xxxx?
I tried numerous variations in the format code and edit expression areas, but no luck.
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?
Thanks in advance