I need to be able to replace only the first occurance of a space character
in a column.
Reason being is the data in the column I am trying to replace seems to have
umpteen space characters after each entry, so a simple replace function
will replace all the spaces after it with what I want!
I have thought of RTRIM to get rid of the spaces after and then replace, I
have also thought of CHARINDEX to find the first occurance of a space and
STUFF to replace it.
I have done my homework on these functions!
But I am having trouble writing such a statement,
I've never written a query which would use more then one function on one
column you see and I am getting confused!
I'll tell you what I want to do in simple steps
Replace only the first found space in a name column, but then if a name has
a middle initial that will be a problem,
Replace that with a dot.
then concatanate '@emailaddress;@emailaddress2' after it
so when SQLServer does the select it will bring back something like
joe.bloggs@emailaddress;emailaddress
But I guess I'd also need joe.n.bloggs@emailaddress;emailaddress
The data in the column looks like this at the moment
joe bloggs
But I guess there may come a time when we have
joe n bloggs, just to complicate things!
What is your advice, and how do I write a query like this
I have been playing around with it in Query Analyser but as I said I am
getting confused and need some help if you don't mind
I'm running under Windows Server 2000 datacenter sp4 with sql server 2000 Sp4
on 16GB Ram.
I enabled the /PAE in the boot.ini file and also enabled the AWE and set the SLQ Server maximum memory to 6144.
Do I need to include the /3GB switch in the boot.ini file?
I'm a little bit confused with the article found on the site. http://support.microsoft.com/default.aspx/kb/274750
Here are the lines.
Use of the /PAE switch in the Boot.ini and the AWE enable option in SQL Server allows SQL Server 2000 to utilize more than 4 GB memory. Without the /PAE switch SQL Server can only utilize up to 3 GB of memory. or-Use of the /3GB switch in the Boot.ini file allows SQL Server 2000 to use up to 3 GB of available memory.
hi.. guys this is simple for you guys how can i make ms access to display the current date in a col.... when i go to insert a new line i how can i make it so that its allready there? i dont have to type it??????
You might just be able to save me downloading and installing SQL Server French...
I'm having localization problems with some .NET code - and I might be able to solve the problem if I know how SQL is parsed in different locales. Say my table MyTable contains a float column FloatColumn. In English, I would say SELECT * FROM MyTable WHERE FloatColumn = 2.3
If my locale was, say for argument's sake, French, would I have to write SELECT * FROM MyTable WHERE FloatColumn = 2,3 ???
Overview. Machine data is fed into DOWNTIMELOG via a Cimplicity. I do not have the ability to change the way that it goes in. It contains the time that a machine has stopped and where the stoppage has occurred. Via a web page I want to give the operator the amount of time the machine stopped. From that they will provide some more detail. As they enter time, I need to subtract what they entered from the total (DOWNTIMELOG). That is the reason I created ENTERED_TIME. [PDNTOTAL_TEMP_VAL0] hold the total time for each stoppage, [TOTTIME] is intended to hold the value that has been accounted for. [DWNCATEGORY_TEMP_VAL0] holds the category and matches up with [DWNCATEGORY]. Also, I need to match Date and Shift. The problem is that DOWNTIMELOG does not capture shift. However, 1st shift occurs between 700 and 1500, 2nd shift occurs between 1500 and 2300, 3rd shift occurs between 2300 and 700. 3rd shift presents a problem as it occurs across 2 dates. Could someone please help me with a query that provides the net between the two tables that is matched by date, shift and category? CREATE TABLE [dbo].[DOWNTIMELOG] ( [timestamp] [datetime] NOT NULL , [DWNTIMESTAMP_VAL0] [varchar] (25) NULL , [UPTIMESTAMP_VAL0] [varchar] (25) NULL , [PDNHOUR_VAL0] [int] NULL , [PDNMIN_VAL0] [int] NULL , [PDNSEC_VAL0] [int] NULL , [PDNTOTAL_TEMP_VAL0] [int] NULL , [DWNMSG_TEMP_VAL0] [int] NULL , [DWNCATEGORY_TEMP_VAL0] [varchar] (50) NULL
Where can i learn about making the sql server execute task when a certain situation happens. Like lets say make the server delete all entrys older than 40 days? Or have a entry deleted automatically when a certain situation happens like lets say it take 4 votes and a entry get deleted how do i make it do that automatically
SQL Server 2000Ya know, it is always the simplest stuff that gets ya !!I am having the hardest time getting a simple piece of code working.Must be brain dead today.Goal: Get the users full name from a stringHere is sample data:"LDAP://blahblahblah/CN=Kevin Jones,OU=DevEng,DC=nobody,DC=priv,DC=com"Code:IF LEN(@strReturnValue) > 0 BEGINSELECT @strReturnValue = SUBSTRING(@strReturnValue,(CHARINDEX('CN=',@strReturnValue)+3),(CHARINDEX(',',@strReturnValue)-1))ENDIt will extract:"Kevin Jones,OU=DevEng,DC=nobody,DC=priv,DC=com"I want it to extract:Kevin JonesThanks.
Hi All,Just wondering if it's any more efficient to use the following SQLstatement to return a partial match from a column as opposed to thesecond statement.SELECT * FROM Table1 WHERE CHARINDEX('value', mycol) > 0Versus:SELECT * FROM Table1 WHERE mycol LIKE '%value%'Does anyone have any thoughts on whether or not either of thesestatements would return a result quicker than the other?Many thanks in advance!Much warmth,Murray
For example if I make a column not null the SQL Script generated. Removes all indexes, keys, etc... than creates a temp tableThan moves the data into the temp tablethen deletes the old tablerenames the temp tableRecreate all the indexes, keys, etc... Is there a structure reason for it? Like now that the DB is being alerted that the column is not null it wants to re insert all the data so it can re-organize it? Because I can just write my own script to the effect of ALTER [TABLE] [COLUMN] PARAM TYPE NOT NULLone line of script that seemingly does the exact same thing.. So I'm just curious when I change a column to non null why doesn't it just generate a simple one liner?
I have this query that calculates the next possible shipping day, based on 3 conditions:
- It has to be a workingday (WORKTIMECONTROL: 1 workingday, 0 holiday) - marked green - There might be extra days (@xdays) required by the process - marked blue - Customer wants their goods to be shipped on special days - marked red:select TOP 1 Transdate from WORKCALENDARDATE where Transdate > @startday and WORKTIMECONTROL = 1 and DATEPART(WEEKDAY,TRANSDATE)-1 in (2,4) and
[code]...
So customer 123456 accepts shipping of goods only on tuesday and thursday as in the above example "... in (2,4)". Multiple shipping days means that the Subquery returns more than one record, which gives me a headache as I don't see how to integrate this portion into my query. I tried to use the stuff function as I formally need a result that can be provided that way; but the format is incorrect as it in varchar, while an array of integer is needed.
DATEPART(WEEKDAY,TRANSDATE)-1 in (select stuff((select ',' + CAST(DELIVERYDAY as nvarchar) from CollectiveShipment where custaccount = '123456' for xml path('')),1,1,''))
This is probably for most of you a very basic question. My goal is to define in a seperate instance from application all infos related to DataSets and DataAdapters that are needed when connecting to a specific database. Why?
First, I would like to be able to have in a single instance everything needed when hooking to a SQL Server Express database and in an other everything needed when hooking to a SQL Server CE database. Then, from application, I would like to be able following a few basic configuration steps to choose from one or the other, or even both in specific cases.
Second, I want to be able to seperate Database related stuff from application so that I can re-use everything from any other application when needed.
I've read several books, articles on the subject and would like to have your opinion as several approaches were illustrated through them. What would you use for this:
CREATE TABLE BILL_DETAIL ([objid] int,[x_billable_to] varchar(19), [x_bill_quantity] int, Â Â Â [x_billable_yn] int, [x_bill_rate] int, Â [COST_TYPE] varchar(19) ) INSERT INTO BILL_DETAIL ([objid], [x_billable_to], [x_bill_quantity], [x_billable_yn], [x_bill_rate],[COST_TYPE])
[code]...
how to get records using stuff keyword as above i want to query using where condition with where objid=1 and should frame output as Parking, Toll only 1 input parameter need to given.
I'm implementing merge replication between SQL CE and sql server 2005 SP2. Here is the problem I'm facing
I have dynamic filters on some tables like
Select * from table1 inner join table2
ON table1.field1 = table2.field3
and table2.flag = true
When table2 flag turns true then all the matching records in table1 should be returned to the subscriber.
But that is not happening. The filter does not return any records to the subscriber even though i see records when i run the query on the server. My take on this is the publisher thinks there is no changes on table1 so there is nothing to update. So to fix that i run dummy updates like
update table1
set [name] = [name]
where table1.field1 in
(Select field2 from table2 where flag=true)
This updates the records and the publisher updates the subscriber the first time. When i set the flag to false, it is supposed to remove the data from the subscriber but i get this error.
The merge process failed because it detected a mismatch between the replication metadata of the two replicas, such that some changes could be lost leading to non-convergence. This could be due to the subscriber not having synchronized within the retention period, or because of one of the replicas being restored to a backup older than retention period, or because of the publisher performing more aggressive cleanup on articles of type download-only and articles with partition_options = 3. (Source: MSSQL_REPL, Error number: MSSQL_REPL-2147199363) Get help: http://help/MSSQL_REPL-2147199363
The common generation watermark is invalid at this replica since it does not exist or metadata for changes not yet propagated may have been cleaned up. (Source: MSSQLServer, Error number: 21800) Get help: http://help/21800
I have no idea whats going on. Any clues, anyone? Thanks for your help.
I have a project. Originally when I created this project, I called it "Project_A", (and I did not create a solution directory).
Doing this created a folder called "Project_A" with the following files within it. I guess these are meta data files and I shouldnt be messing around with them. Anyway, I see
(1) Package.dtsx (The default package that gets created with a new project) (2) Project_A.database (3) Project_A.dtproj (4) Project_A.dtproj.user (5) Project_A.sln (6) Project_A.suo
When I rename my project in the Solution Explorer to "Project_B", the following happens in my folder.
Everything remains the same except Project_A.dtproj gets renamed to Project_B.dtproj and a new file called Project_B.dtproj.user gets created.
Now, my questions.
Question 1: What if I wanted my folder to have the same name as my project? Can I just change the folder name in Windows Explorer without having any ill-behaviors later on? I always have 1 project to 1 solution so I want to use the convention of keeping my folder named the same as my project.
Question 2: What about the files that are still named with Project_A dot blah blah. Is it bad if I renamed these to Project_B dot blah blah?
A whole bunch of exciting new stuff announced around the Visual Studio Express Editions today. VS Express is now free for good, not just 1 year (just like SQL Server!). Also lots of great new content and fun projects to do.
See Dan Fernandez's blog posting at http://blogs.msdn.com/danielfe/archive/2006/04/19/579109.aspx for lots of details.
Our needs are on yearly basis and on monthly basis. We're forced to keep up five years for the majority of the production tables. In terms of years, I see three ways:
1.Create all the ranges for a FILEGROUP with a only NDF
2004, 2005, 2006 => FG1 => ONE.NDF
2.Create all the ranges for a FILEGROUP along with more than one NDF.
2004, 2005, 2006 => FG1 => ONE.NDF TWO.NDF
3.Create each range to a FILEGROUP where there will be one NDF or (n) NDF
I have an address field that has data that needs to parsed. The data has different formats. I have tried charindex and substring but I am not getting the results I need.
Here is what the data looks like:
Paper Mill Rd 3332 Allegheny Ave 13 1/2 Balto Nat'l Pike 6600 R-2 Rolling Rd N 3030
Here is the results I am looking for:
street dir street number street name street type street unit 3332 Paper Mill Rd 13 1/2 Allegheny Ave 6600 Balto Nat'l Pike R-2 N 3030 Rolling Rd
Hi, The following stored procedure creates this error, "Syntax error converting the varchar value '17,18,405,408,406,493' to a column of data type int." I tried using a Convert and a Cast function but it still failed. I then read something about using the CharIndex function, but I can't figure it out. Any help would be much appreciated in getting this to work. Thanks
CREATE PROCEDURE [ResourceProducts] @ProductHtml varchar(200) AS
SELECT Resources.ResourceId, Resources.Heading FROM Resources INNER JOIN Products ON Resources.ResourceId IN (Products.Resources) Where Products.ProductHtml = @ProductHtml GROUP BY Resources.Heading, Resources.ResourceId GO
Hi I have a column col1 in a table having below strings. How do I get only the required part of the string as I explaind below. (Needed strings are followed by i.e as below).
1. But the above code only works if the string starts with 'ID='. 2. Because the col1 has strings starts with different letter this code fail to excute and gives error 3. Pathindex with regular express '(patindex('%[^0-9]%',v)-1)' cannot be used because it gets anynumber in the string - string has mixer of numbers and letters
SO IF ANYONE LET ME KNOW HOW TO USE IF or CASE with 'charindex' or any other method to get the above said result would be greatfull.
UserID=176&StoreID=210 sometimes there is more between the two so im tring to use a CHARINDEX and Substring
Substring(Parameters, CHARINDEX('UserID', Parameters),3) When I do this it brings back just the 'USE' I need it to bring back the number associated with it. In this case the 176 how do i get that number?
I have used CHARINDEX successfully to search for a substring going from left to right in a string, but how do I search from the end of a string and go backwards?
Here is a sample of the problem I am trying to solve. Here is a list of cities, some with two names and some with more than two. I need a way to extract only those cities with two names. I wanted to do this by locating the first space in the string and the last space in the string. If the first space equals the last space, then the city should only be a two word city. Otherwise it is a three name city or greater. So what string functions can I use to locate the first and subsequent spaces in a string?
Here is the current version of my SQL statement:
SELECT City.City, CHARINDEX (' ' ,City.City) AS First_Space FROM City WHERE CHARINDEX (' ' ,City.City) <>0
AND HERE IS A SMALL SAMPLE OF WHAT IS RETURNED:
MARINE CITY7 MARINE ON SAINT CROIX7 MARION CENTER7 MARION HEIGHTS7 HIGH RIDGE5 HIGH ROLLS MOUNTAIN PARK5 HIGH SHOALS5
Thanks to all who understand and can offer usable advice in my dilemma.
I have a field containing a string with '/' in it multiple times.How can I return the charindex of the last occurance of '/' in thestring?Regards,Ciarán
What is the best way to essentially use the charindex(find) functionif the value is could be more than one variable (A or B or C)I can't seem to get an "or", "if" or "select if" to workBelow is the code that works, I need 'GOV' to be either 'GOV' or 'FWD'or 'LIB'sqlstring = "SELECT Distinct substring([exposurename]," _& Len(Worksheets(4).Range("j5") & "_VAR_" _&Worksheets(4).Range("C4").Offset(Worksheets(4).Range("c3"), 0)) + 2 _& ",charindex('GOV',[exposurename])-" &Len(Worksheets(4).Range("j5") _& "_VAR_" &Worksheets(4).Range("C4").Offset(Worksheets(4).Range("c3"), 0)) + 3 _& ") AS Drivergrp2 " _& "FROM mars.dbo.mroInventoryProductGreeks_Latest" _& "Where producttype = 'creditdefaultswap' " _& "AND exposureName like '" &Worksheets(4).Range("j5") & "_VAR_" &Worksheets(4).Range("C4").Offset(Worksheets(4).Range("c3"), 0) & "%' "_Much appreciation if you can help
I'm trying to do some basic stuff in SQL Server 2005 that I could do in Oracle. I'm sure there's a way to do these things, I just don't know how. My most immediate question is this: Is there any easy way to run a SQL script and have it spool to HTML tables to a file? The 'results to text' from SSMS is not adequate for display purposes. I'm trying to display table/column descriptions. Beyond that, does anyone know of any good resources that provide an 'Oracle to SQL Server' matrix so I can help figure out how to do some of these things in SQL Server? Thanks in advance, Mike
BACKGROUND: I just set up a network with 2 computers (direct cable network, no routers/hubs), one computer is running Windows 2000 Server and the other is running Windows XP Professional. They both recognize each other and I can access each computer on either computer. The WinXP computer has SQL Server 2005 express edition installed on it.
BASIC PROBLEM: I am trying to install a database server on my Windows 2000 Server OS computer. This computer is a 350MHz, 256mb RAM computer. How would I go about doing this?
I have the following query that supposes to merge multiple result in a single one and put it into a temporary table:
SELECT DISTINCT [AlphaExtension], STUFF((SELECT A.[NoteText] + '< BR />' FROM #temp A WHERE A.[AlphaExtension]=B.[AlphaExtension] FOR XML PATH('')),1,1,'') As [NoteText] FROM #temp B GROUP BY [AlphaExtension], [NoteText]
It is working fine unless by a simple detail. If you look at the second line of the query you will see that I am stuffing together a < BR /> tag (break line) because the contents of the field is going to be spitted directly to the screen and I want that the multiple results be displayed in different lines.
OK, the issue is that it is stuffing & lt ; BR / & gt ; instead < BR /> and therefore the browser is displaying the tag instead to break a line.
I have a column which needs to be 9 digits long. The first four digits are independent from the other digits. The following digits need to be 5 and I have to add leading zeros if they're less than 5. This is an awful design and I'd love to change it but I can't.
Hi all,I'm hoping somebody here might be able to point me in the rightdirection.Basically, I'm looking for a "good" way to build tables such thatdifferent kinds of stuff can grouped.For example, let's say I have "rooms" and they can contain "people"and/or "books" and/or "furniture" -- each of those objects (rooms,people, books, furniture, etc) may have their own attributes -- but theidea is that a room may contain 0 or more of each of the others.I'll need to do lots of selects to retrieve the contents of specificrooms.Is there a standard / good way to go about this sort of thing?Somehow giving people, books, furniture a room_ID (foreign key) seems abit clumbsy -- ie, do I have to run a select for each?thanks kindly, -Scott
