Hello, I have this Access 2K query that I need to re-create in MS SQL
Server 2000, so I'm using the Query Analyzer to test it.
One of the Access fields stores the home phone number. In the Access
query, 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) then
it leaves it alone. That Access query is using immediate ifs to
accomplish that task.
Does anyone have any idea how to copy this behavior into SQL Server
2000? 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:
I am trying to Strip off the Numbers witha Delimited List and just retain the Name of the Persons. but unable to do it. is there any function or code to do that in SQL
932908` James Fleser,935992` Prakash Sinha
Stripping off Numbers for the Above and Just retain the Names..
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!
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
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...
We are having a problem with one of our SQL servers, and in comparing it to the backup server which is working fine, I noticed some differences. I attempted to correct the differences, but no luck. The dell server has 4 dual-core processors and at one point hyper-threading was enabled. One of our DBAs recommended that it be turned off. We didn't have any major problems until recently and it seems that getting this setting right is the lynchpin. Any suggestions?
John
EXEC sys.sp_configure N'show advanced options', N'1' RECONFIGURE WITH OVERRIDE
GO
EXEC sys.sp_configure N'affinity mask', N'0'
GO
EXEC sys.sp_configure N'affinity I/O mask', N'0'
GO
RECONFIGURE WITH OVERRIDE
GO
EXEC sys.sp_configure N'show advanced options', N'0' RECONFIGURE WITH OVERRIDE
GO
-----------------------------------------
Configuration option 'show advanced options' changed from 0 to 1. Run the RECONFIGURE statement to install.
Msg 5832, Level 16, State 1, Line 1
The affinity mask specified does not match the CPU mask on this system.
Msg 15123, Level 16, State 1, Procedure sp_configure, Line 51
The configuration option 'affinity mask' does not exist, or it may be an advanced option.
Msg 15123, Level 16, State 1, Procedure sp_configure, Line 51
The configuration option 'affinity I/O mask' does not exist, or it may be an advanced option.
Msg 5832, Level 16, State 1, Line 1
The affinity mask specified does not match the CPU mask on this system.
Configuration option 'show advanced options' changed from 1 to 0. Run the RECONFIGURE statement to install.
Msg 5832, Level 16, State 1, Line 1
The affinity mask specified does not match the CPU mask on this system.
The Above is my database, I need help in retrieving the X_Co and the Y_Co using values of rcv_A, rcv_B and rcv_C to compare with the Mean_A, Mean_B, Mean_C. The values of rcv_A, rcv_B and rcv_C are instances of values that are not exact of the mean columns , and we want is to compare it against our database and retrieve the row that is the closest to the rcv_A, rcv_B and rcv_C.
Here is an example of what i need. Let's say my rcv_A = 71, rcv_B = 73 and rcv_C = 70.8, so the row with mean value closest would be row 1, followed by row 2, then row 3.
So the result i hope to retrieve is in order of the closest value and i only need the X_Co and Y_Co. This is what i want
I have a table into which users can enter a To and From range, and an application returns all the values in between. Naturally the users can enter to and from values that don't exist, as all they need to do is satisfy a "between" expression. eg they can enter '1100' to '1399' and even though neither value actually exists in the data, the query will return all the values that fall into the desired range.
However the application now demands a bit more of a sophisticated approach. The target data may have the form 'nn-nnnn-nn' where n is '0' to '9', and the user wants to select all the values (in pseudo-code) which satisfy a pattern like '%-7040-%' and '%-7400-%'
A simple LIKE expression with a mask containing a RegEx won't work - ie LIKE '%-7[0-4][0-4]0-%' doesn't work because (eg) '00-7150-00' won't satisfy the mask because of the 5 falling outside [0-4], although it is within the range I want.
I have come up with a solution to this problem by using MIN and MAX aggregate functions on LIKE [From] and LIKE [To] respectively and this works fine. eg
"SELECT A.<something> from <target_table1> A, <table_with_ranges> B WHERE A.<something> BETWEEN (SELECT MIN(X.<something>) FROM <target_table1> X WHERE X.<something> LIKE B.[From]) AND (SELECT MAX(X.<something>) FROM <target_table1> X WHERE X.<something> LIKE B.[To])"
It's actually quite a bit more complicated than that - I have simplified the SQL a bit, but that is the basic algorithm.
The problem is that for the BETWEEN expression to work, both the MAX and MIN functions have to return a value that is not NULL- ie there has to be a value in the target table which satisfies the mask. In the application, this is not always going to be the case - Users will want to create large ranges to allow for future growth so every time they enter new data into target_table1 the don't then have to go out and redfine the [From] and [To] ranges to satisfy the new data. What I really need is an SQL expression along the lines of "SELECT .... FROM ... WHERE A BETWEEN <pattern1> AND <pattern2>" where <pattern1> and <pattern2> contain wildcards.
Anyone got any suggestions??? By the way, using SUBSTRING or other string functions is NOT an option for a variety of reasons.
I have a dual 64 bit quad core server with 16 GB of memory. We are going to run an application server and SQL Server 2005 SP2 CU4 64 bit on this hardware, but we only want to purchase a single CPU license for SQL Server. The obvious choice is to use the affinity settings to prevent SQL Server from using one of the CPUs.
Initially, the development team simply went into SSMS and unchecked affinity mask and affinity io mask for the first four processors. This appeared to work fine in their testing. A problem arose when we started monitoring the maintenance plan and saw that the database integrity check was failing. The root problem was this invalid state that the affinity masks were in.
I have seen a lot of documentation stating the SQL Server will ignore an invalid mask setting, but in our testing, it appears that SQL Server respects the setting. For example, when we set CPU 7 to be available for processing and IO, Performance Monitor showed that only CPU 7 was used during a load test.
So from our preliminary testing, it looks like SQL Server will use a single CPU for both processing and IO if you tell it to. Is there some other reason why these affinity settings need to be mutually exclusive? Is there a test I can run that can illustrate why?
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.
I want to strip off the last three characters from an item number. The only thing is tht every item number is not the same lenght. The last three characters of this number are packing codes that I do not need. Fore example I can have all these numbers:
Does anyone know how to get rid of rtf tags that are stored in the table? I need to filter out the data and wondering if there is a utility on the SQL Server that can do it.
Has anyone had to deal with removing punctuation (commas, apostrophes,etc.) from a column? What is the most efficient way to have thesecharacters automatically removed from the column?
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 ?
Hi everyone, I have a sql quey that selectes phoneNumbers from the database. Problem is some phone numbers have hypens in it and some doesn't. Is there any way in sql so that I can remove hyphens from the phone numbers
some numbers are like this 213-456-9999 and some are 2136789999
Question - if you had to completely strip all permissions from all databases in an instance and reset them, assuming you have metadata to support rebuilding the permissions, what steps would you follow? I can handle the iterating through each database, but at the database level, what steps would you take?
The reason I inquire on this is I currently have a job that I inherited that does just this. But it's buggy and was also written in for SQL Server 2000. With some of the changes in 2005, a few bugs have crept in, etc. And I would like to confirm my thoughts this. Or, if your opinion is why are you wasting your time on this? Then that's fine to and I'll review any constructive comments you may have.
I'm having trouble coming up with a function that will allow me to display only the date and time from a string in the following format:
JSMITH 1/1/2006 1:00:00AM
I've tried using substr with a negative position value, but since the date and time for each instance can be different, with it being anywhere from 18-21 characters, if the length is anything less than 20-21 characters, it will return part of the end of the username. What function can I use to only retrieve the date and time?
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'm trying to strip out the First & Last Name from an Email Address. The email address is formatted as "FirstName.LastName@emailaddress.com" but sometimes a middle initial is used (ex: "First.M.Last@emailaddress.com").
I can locate the '@' sign and the first '.' period(from left to right) but need to find the first '.' from the left of the '@' sign.
I'm doing this in a view.
Here's some sample data and what I've tried:
CREATE TABLE dbo.Table1 (EmailAddr VARCHAR(255)) INSERT INTO dbo.Table1 (EmailAddr) SELECT 'Andy.Smith@gmaddr.com' UNION ALL SELECT 'Betty.Lee-Jones@gmaddr.com'
-- Prepare sample data DECLARE@Sample TABLE (ID INT, NetworkIP VARCHAR(15), SubnetMask VARCHAR(15))
INSERT@Sample SELECT1, '192.168.1.0', '255.255.255.128' UNION ALL SELECT2, '10.1.1.64', '255.255.255.240' UNION ALL SELECT3, '172.16.11.0', '255.255.255.252'
-- Show the expected output SELECTID, NetworkIP, SubnetMask, dbo.fnIsInRangeIP('192.168.1.5', NetworkIP, SubnetMask) AS IsInRange FROM@SampleAnd here is the function codeCREATE FUNCTION dbo.fnIsInRangeIP ( @IP VARCHAR(15), @NetIP VARCHAR(15), @MaskIP VARCHAR(15) ) RETURNS BIT AS BEGIN RETURNCASE WHENCAST(PARSENAME(@IP, 4) AS TINYINT) & CAST(PARSENAME(@MaskIP, 4) AS TINYINT) = CAST(PARSENAME(@NetIP, 4) AS TINYINT) & CAST(PARSENAME(@MaskIP, 4) AS TINYINT) AND CAST(PARSENAME(@IP, 3) AS TINYINT) & CAST(PARSENAME(@MaskIP, 3) AS TINYINT) = CAST(PARSENAME(@NetIP, 3) AS TINYINT) & CAST(PARSENAME(@MaskIP, 3) AS TINYINT) AND CAST(PARSENAME(@IP, 2) AS TINYINT) & CAST(PARSENAME(@MaskIP, 2) AS TINYINT) = CAST(PARSENAME(@NetIP, 2) AS TINYINT) & CAST(PARSENAME(@MaskIP, 2) AS TINYINT) AND CAST(PARSENAME(@IP, 1) AS TINYINT) & CAST(PARSENAME(@MaskIP, 1) AS TINYINT) = CAST(PARSENAME(@NetIP, 1) AS TINYINT) & CAST(PARSENAME(@MaskIP, 1) AS TINYINT) THEN1 ELSE0 END ENDE 12°55'05.25" N 56°04'39.16"
if a decimal point exists. But if one does not the CharIndex comes back 0 and my equation does not work correctly.
I need to pull the data from the table, along with many other fields. How do I do this without using a cursor? I have millions of rows so need a solution that will be quick.
I'm using the ForEach File task and masking the files I want to process as ABC??.TXT. It all works well until I drop a file there like ABC12 Copy.TXT, which I don't expect to be process, but the loop still picks it up. Am I using the wrong wildcards?
sorry for my question, maybe it seems un professional but I need to know the answer,
is it possible to enter the data in a sql table in a specific format and how it could be?
for example I want to have a mask in the table to enter the data in this format ##.## so it will not accept any other data to be typed in without this format.
As part of a data search project I need to be able to strip all non numeric characters from a text field. The field contains various forms of phone number in various formats. In order to search on it I am going to remove all non numeric characters from the input criteria and from the data being searched.
In order to do this I decided on using a SQL Server custom function: Pass in field. Loop through all chars, test against asci values for number range. return only numernic data concatenated into a string.
Are there any other more efficient ways of going about this?
