I have some data-- specifically times for cell phone usage in the format of (7:00, 15:51, 1,200:45, etc). I need to find a way to remove the ":" and the ","-- sum the data and then return it to its previous format of (7:00, 15:51, 1,200:45, etc). Does anyone have some code they could post??
Hi,In SQL SERVER 2005 Database I have a field called MedNames with values such as "sodium % 34ml" "desx chloride 9 % 76ml"I need to return the words before and including % so "sodium % 34ml" should return ""sodium % " Is it possible to do it in a select statement? I need to populate a dropdownlist with the shortened names.Thanks
Hello there... I am looking for the function that is the same as InStr in Access for SQL server. I have a column that has format like this.. Lastname,Firstname Middlename... This column doesn't separate each one of them. However I need to separate Lastname and Firstname and Middlename.. I was told that in Access there is function(InStr) that can find a position of comma and separate it as Lastname like that.... I was searching BOL but I couldn't find like this function in SQL Server.. So I need help:-)))) Because everybody has a different length of the lastname, I have a problem. I can not use SUBSTRING or LEFT or RIGHT because of the varying position of comma ...
In VB there is InStr, in Excel you have SEARCH. what function can i use to search a string for a specific string. ie: how can i find where a space is in someones name?
our database has a table that is used for security. it contains a user code, user name and passcode. i need to split the username (currently forename and surname in same field) into two, around the space.
in VB i would write something like strFName = left(<usernamefield>,InStr(1,<usernamefield>," ")-1) to get the forename and similar to get the surname. how can i do this in SQL so that a view will supply the data apprpriately?
one of the reasons i want to do this is so that i can sort the users by surname! another is so that i can give options on their usercode - a combination of forename initial and 2 characters from surname.
TASK: At my work we want to categorize and summarize all our IIS web logs and make statistics from it and such. What I need to do is take the browser type from a certain column in the table. All the information is stored in 1 column, and I figure an instr function would be best to do this. I am new to SQL, so I was told to look up the cursor function. In summary, I want to take all the IIS data and match it up against a defined table and then have a sum function for each browser.
Here are some examples of what the column data looks like: (found in the [csMethod] column
I made a define table which lists an ID (primary key) and instr to search for as well as the full browser name. (define.browser)
ID# INSTR# BROWSER NAME ############################### 1___Opera+7_______Opera 7 2___Opera/9_______Opera 9 3___Safari/_______Safari 4___Firefox/1.0___Mozilla Firefox 1.0 5___Firefox/1.5___Mozilla Firefox 1.5 6___Firefox/2.0___Mozilla Firefox 2.0 7___MSIE+5.5______Microsoft Internet Explorer 5.5 8___MSIE+5________Microsoft Internet Explorer 5 9___MSIE+6________Microsoft Internet Explorer 6 10___MSIE+7________Microsoft Internet Explorer 7 11_________________OTHER BROWSER
I am having problems getting a cursor to work. Are there any good tutorials out there, or can anyone be of assistance. Thank you in advance.
I am trying to convert a complex function from Oracle to SQL Serverand have come across Oracle's Instr() function. I see SQL Server hasCHARINDEX() which is similar, however it does not provide some keyfunctionality I need. Here is an example of the Oracle code:if Instr( sTg , cDelim, 1, 3 ) > 0 thensd := SubStr( sTg, Instr( sTg , cDelim, 1, 1 ) + 1, Instr( sTg,cDelim, 1, 2 ) - Instr( sTg , cDelim, 1, 1 ) - 1)end if;Has anybody converted anything similar to this within SQL Server? Anyhelp is GREATLY appreciated!Thanks.
Hi everybody,I was looking for an equivalent ORACLE INSTR Function in MSSQL but Idon´t found it and I don´t know if it exist so I must to write it andthis is the code. Maybe it will be helful to you:/************************************************** *************************Description:Looks for a string inside another string and returns an integerthat correspond to the position of first ocurrence.Parameters:Input:- strSource. Contains the string where the functions look for theother string- strToFind. Contains the string to look for inside strSourceSalida:- Integer value indicating the position of first occurrence ofstrToFind in strSource************************************************** *************************/CREATE FUNCTION posSubString(@strSource varchar(400),@strToFind varchar(200)) RETURNS intASBEGINDECLARE@position int,@maxPos int,@longSubStr int,@res int,@strSub varchar(200)SET @position = 0SET @res = 0SET @longSubStr = LEN(RTIRM(LTRIM(@strToFind)))SET @maxPos = LEN(@strSource) - @longSubStrWHILE (@position <= @strToFind)BEGINSET @strSub = SUBSTRING(@strSource, @position, @longSubStr)IF (@strToFind = @StrSub)BEGINSET @res = @position - 1RETURN @resENDELSESET @position = @position + 1ENDRETURN @resENDAlonso
I went to Microsoft to find some info about the function Instr. I need to perform a search with a string similar to their example I found below. Can anyone explain to me Microsoft's example?? I am little confused by the parameters used and the explanation it gives back to me??
Dim SearchString, SearchChar, MyPos SearchString ="XXpXXpXXPXXP" ' String to search in. SearchChar = "P" ' Search for "P". MyPos = Instr(4, SearchString, SearchChar, 1) ' A textual comparison starting at position 4. Returns 6. MyPos = Instr(1, SearchString, SearchChar, 0) ' A binary comparison starting at position 1. Returns 9. MyPos = Instr(SearchString, SearchChar) ' Comparison is binary by default (last argument is omitted). Returns 9. MyPos = Instr(1, SearchString, "W") ' A binary comparison starting at position 1. Returns 0 ("W" is not found).
My problem is this:
I need to scan within SearchString for blanks/spaces characters. When I find one, then place the values to the left and right of it in seperate columns. For example, I would need to scan 'John Smith A' and then place 'John' in FirstName column, 'Smith' in LastName column, and 'A' in MidName column.
I think this is how my code would read, but I am confused on how to place the results into my table to the correct columns?
my search string would be SearchString = 'John Smith A' my SearchChar would be SearchChar = ' ' (note I am searching for a space/blank character)
So would then my code be like:
Dim SearchString, SearchChar, MyPos SearchString = 'John Smith A' SearchChar = ' ' MyPos = Instr(1, SearchString, SearchChar, 0)
How do I get whatever is returned from the Instr function to a column in a table??
My SQL is extremly rusted so I need some help with a very basic function. I have a character field which is built up using a category code + '-' + number. The problem I have is that the category codes are all different lengths and the items were added using 9 instead of 09. I'm trying to clean up the data so that the same item with e.g. category code DZ20 cannot be added as DZ20-1 and DZ20-01. How do I find the position of the '-' in the Query Analyser for MSSQL 2000?
string2 is the substring to search for in string1.
start_position is the position in string1 where the search will start. This argument is optional. If omitted, it defaults to 1. The first position in the string is 1. If the start_position is negative, the function counts back start_position number of characters from the end of string1 and then searches towards the beginning of string1.
nth_appearance is the nth appearance of string2. This is optional. If omiited, it defaults to 1.
In SQL Server, we are having CHARINDEX and PATINDEX functions. But they will not accept the fourth paremeter (nth_appearance)
I have a field that contains a town Â ex : New York. Sometimes, the field will have brackets to be more specific ex: New York (Brooklyn). What I need is a formula that will return only "Brooklyn" if there are brackets or Â the whole field if there are no brackets.Here's what I thought would do the trick :
So if InStr returns more than 0, meaning that I have brackets, I'll split at the "(", get the second part and remove the last character which is the closing bracket I don't want. If there is no bracket, I'll return the whole field.Somehow, when there is no bracket, my report overview shows me "#Error" as if the first Iif was true and what's in the true part is not actually possible, because it's trying to take the second part of a split that couldn't split because there is no bracket to split.
I tried to remove AdventureWorksDB in the "Add or Remove Programs" of Contol Panel and I got the following errors: (1) AdventureWorksDB Error 1326: Error getting file security: CProgram FilesMicrosoft SQL ServerMSSQL1MSSQLGetLastError: 5. |OK| and (2) Add or Remove Programs Fatal Error during installation (after I clicked the |OK| button). Please help and tell me how I can solve this problem.
I have uninstalled the CTP version of the SQL Server express so that I can install the released version but CTP version is still listed in the add/remove program list but without the change/remove button. I have been to different sites to find information on cleaning this up and I have ran all the uninstall tool I can find but the problem still prevails. I cannot install the released version without completely getting rid of the CTP version. Please help anyone.
I have a query that for one reason or another produces duplicate information in the result set. I have tried using DISTINCT and GROUP BY to remove the duplicates but because of the nature of the data I cannot get this to work, here is an example fo the data I am working with
ID Name Add1 Add2 1 Matt 16 Nowhere St Glasgow 1 Matt 16 Nowhere St Glasgow, Scotland 2 Jim 23 Blue St G65 TX 3 Bill 45 Red St 3 Bill 45 red St London
The problem is that a user can have one or more addresses!! I would like to be able to remove the duplicates by keeping the first duplicate ID that appears and getting rid of the second one. Any ideas?
I have a pivot query returning data ordered by a timestamp. In any give second only a few of the columns will have data, the remaining columns are NULL. I would like to default the NULL data values to the previous NON NULL value by timestamp.
Hey all, I have a profile table for members of a website that allows them to upload an avatar for use throughout the site. I decided to add the option to just remove the avatar if they want to no longer use one at all and I am stuck. I am running an update statement against thier entire profile incase they change more then just removing thier avatar but of course when I try this statement. myCommand.Parameters.AddWithValue("@avatar", "") I get a conversion error, which makes sence. So what instead of "" do I need for code to just insert empty data to overwirte thier current avatar data. Any help would be great, thanks for you time.
In tweaking performance of tempdb by adding add'l data files I want to reset back to defaults and remove all add'l files I've created. Â I was not able to do it for most as they were in use, but by starting the server in single-user mode with all other sql services shut off, and using sqlcmd I was able to use the ALTER DATABASE tempdb REMOVE FILE <tempdev#> to remove the files... except for one.
I am having a hard time removing my SQL instance inside the Add/Remove program. After i select the SQL Instance name and then I tried to remove it but it won't allow me to delete it. There isn't any error message or whatsoever. Actually, when i try to log it in my SQL Management studio, that certain sql instance name is not existing according to the message box. Is there any way to remove the Sql Instance in my system?
We are running SQL server 2003 with SP3. I'm trying to shrink a data files with the emptyfile option so I can eventually remove the file using the alter database command. However, I get the following error message when I run the alter database command:
Error: the file PRADATA4 cannot be removed because it is not empty.
The file that I'm trying to remove still has 62 extents on it. I looked MS Knowledge base 254253 and 279511 on this problem but they say it is corrected by SQL server 7.0 with service pack 3. commands that I'm running are as follows:
1) USE PRA DBCC TRACEON(8901) DBCC SHRINKFILE ('PRADATA4', EMPTYFILE) DBCC TRACEOFF(8901)
2) USE PRA GO ALTERDATABASE PRA REMOVE FILE PRADATA4 GO
Yesterday I had posted a query of How to transfer the Data from AS400 files to Sql 2005 tables with DTS and the error I received for the same. I am not able to view the replies since it has been deleted. Pls repost the same. I posted the query expecting for a solution but instead my question was deleted..... the very purpose of forum is defeated.
I have tried to set up the Management Data Warehouse on one of our production servers, but it is not working (not collecting any data) so I want to completely remove it and try the installation again. There does not seem to be any remove/uninstall option.
How to completely remove the Management Data Warehouse?
Version: Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (X64) Mar 29 2009 10:11:52 Copyright (c) 1988-2008 Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7600: )