Hi
My question is about the INSTR function of MSAccess
I want to know
Goodbye at every
I would you like to know if in TransactSQL of SQLServer7 exist a function that return the position of a string into an other string .
that is a Instr function MSAccess like
INSTR([Start,]expression1,expression2[,Comparison])
I'm sorry for my bad English
Thank you for everything
Emiliano
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
View 1 Replies View RelatedI am using the Instr(), Len(), and Mid() function in my SQL query and I keep getting errors stating those are not recognized functions. IS this correct? are there any equivelants?
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 ...
Thanks in advance
Jay
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.
any help welcomed.
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
View 1 Replies View RelatedI 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??
Any help would be great.
Rey
Hi All
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?
We have a static class that makes an HTTPWebRequest to get XML data from one of our vendors. We use this as input to a stored proc in SQLServer2005. When I compile this class and call it from a console application in visual studio it executes in milliseconds, everytime. When I compile it, create the assembly and clr function and execute it in SQLServer, it takes around 14 seconds to execute the first time, then on subsequent requests it is again really fast, until I wait for 10 seconds and re-execute, once again it is slow the first time and then fast on subsequent requests. We do not see this behavior when executing outside SQLServer. Makes me think that some sort of authentication is perhaps taking place the first time the function is run in SQLServer? I have no idea how to debug this further. Anyone seen this before or have any ideas?
Here is the class:
Code Snippet
using System;
using System.Collections.Generic;
using System.Text;
using System.Net;
using System.IO;
namespace Predict.Services
{
public static class Foo
{
public static string GetIntradayQuote(string symbol)
{
string returnQuote = "";
HttpWebRequest request = (HttpWebRequest)(WebRequest.Create("http://data.predict.com/predictws/detailed_quote.html?syms=" + symbol + "&fields=1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,28,30"));
request.Timeout = 1000;
HttpWebResponse response = (HttpWebResponse)(request.GetResponse());
StreamReader streamReader = new StreamReader(response.GetResponseStream());
returnQuote = streamReader.ReadToEnd();
streamReader.Close();
response.Close();
return returnQuote;
}
}
}
When I run call it from a console app it is fine.
I compile it into a dll and then create the assembly and function as follows:
Code Snippet
drop function fnTestGetIntradayQuoteXML_SJS
go
drop assembly TestGetIntradayQuoteXML_SJS
go
create ASSEMBLY TestGetIntradayQuoteXML_SJS from 'c:DataBackupsCLRLibrariesTestGetIntradayQuote_SJS.dll' WITH PERMISSION_SET = EXTERNAL_ACCESS
go
CREATE FUNCTION fnTestGetIntradayQuoteXML_SJS(@SymbolList nvarchar(max)) RETURNS nvarchar(max) AS EXTERNAL NAME TestGetIntradayQuoteXML_SJS.[Predict.Services.Foo].GetIntraDayQuote
go
declare @testing nvarchar(max)
set @testing = dbo.fnTestGetIntradayQuoteXML_SJS('goog')
print @testing
When I execute the function as above, again, really slow the first time, then fast on subsequent calls. Could there be something wrong with the code, or some headers that need to be set differently to operate from the CLR in SQLServer?
Regards,
Skipper.
how to implement the replication with the System Stored Procedures or the Transact-SQL ?
i want to encapsula my replication and make it be reusable and ease to use.So i want to do the replication in a script file.how can i do this?
Thanks.
I had built a one calander in SQLSERVER
View 1 Replies View RelatedHi I need a function which gets 3 parameters and input and returns 4 integers as output.
I don't know how to return 4 integers as output
any idea is appreciated.
Hi ,
I 'm working with visual studio 2005 and I have created an SQLServer Project.
I'm using the CLR functionality which comes with SQLserver 2005. This means that I can write VB.nEt code and use it inside Sqlserver 2005.So far so good.
I am now inside the .NET
I have created a Function(must remind you that I have created an SQLserver Project) which takes two string arguments. The date value in a string format and the string format.
In Our case the function returns a string.It will return a datetime although.
So we have
Dim Datetime_Val As DateTime = Nothing
Dim Date_Val As Date = Nothing
Dim StrTemp As String = ""
Dim StrDateTemp As String = Nothing
Dim StrTimeTemp As String = Nothing
Dim ls_return As String = Nothing
Dim lindexof As Integer
Dim Counter As Integer = 0
lindexof = 0
Select Case StrFormat
Case "DD-MM-YYYY HH24:MIS"
For Counter = 1 To 2
lindexof = StrDate.IndexOf("-", lindexof + 1)
Next
lindexof += 5
StrDateTemp = StrDate.Substring(0, lindexof).Trim
StrTimeTemp = StrDate.Substring(StrDateTemp.Length, StrDate.Length - StrDateTemp.Length).Trim
ls_return = StrDateTemp & " " & StrTimeTemp
End Select
The above is a simple code. As you can see I'm trying to convert the TO_DATE function ,which work with ORACLE, to make it work with SQLServer 2005.
I've been trying unsuccessfully to combine the variables StrDateTemp and StrTimeTemp into a datetime value. I used the following code but nothing
Datetime_Val = CDate(StrDateTemp & " " & StrTimeTemp)
Didn't work
Datetime_Val = Convert.ToDateTime(StrDateTemp & " " & StrTimeTemp)
Didn't work
Datetime_Val = DateTime.Parse(StrDateTemp & " " & StrTimeTemp)
Didn't work
Inside SQlServer I used this SQL statement
Select dbo.TO_DATE('31-12-1990 00:26:46','DD-MM-YYYY HH24:MIS')
But I am receiveing an error. I want to avoid changing all of my applications with a specific format.This sql statement without the dbo prefix I'm using in Oracle. I want to keep the format of the SQL and let VB.NET do the parsing for me. It is easier for me to put in my SQLs the dbo infront rather changing the complete SQL.
I have two questions . How am I going to create a TO_DATE function which Oracle uses and write something similar in SQLserver ?
And If I cannot do that how am I going to get the database 's datetime format and create with VB.NET the Datetime value from the two variables ?
My problem I believe is quite complex. I would be mostly appreciated if you could help me on this.
Thank you
i have a quite strange condition...when i add some value in database with getdate() function it only returns date and minute not the seconds...does somebody have an experience about this
View 3 Replies View RelatedI use a database that has user names stored in Encrypted format usingthe following API.Declare Sub Encrypt2 Lib "QPRO32.DLL" (ByVal Work As String, ByValPASSWORD As String)Every time i require the user name i have to again decrypt the nameusing the same function.My problem is that when i fetch a large number of records i have toloop through every record and call the encrypt function for eachrecord.Instead of binding the recordset to my control i need to loopthrough and fill my controlA MSHFlexGrid in Vb6.0.Is there a way out to this problem that will make my record populatiogfaster withoutout changing the current Encrypted users.Thanx in Advance
View 2 Replies View Relatedis there a sql keyword for find or instr?
i have a field i wish to make into two and i need the position of a string "-" in the field so i can do a select right and copy that data to a new colm
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
Mozilla/4.0+(compatible;+MSIE+6.0;+Windows+NT+5.1;+SV1)
Mozilla/4.0+(compatible;+MSIE+6.0;+Windows+NT+5.1;+SV1;+.NET+CLR+2.0.50727)
Mozilla/4.0+(compatible;+MSIE+7.0;+Windows+NT+5.1;+.NET+CLR+2.0.50727;+.NET+CLR+3.0.04506.30;+InfoPath.2;+.NET+CLR+1.1.4322)
Mozilla/5.0+(Windows;+U;+Windows+NT+5.1;+en-US;+rv:1.8.0.8)+Gecko/20061025+Firefox/1.5.0.8
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)
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.
View 5 Replies View Related
Greetings All
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??
thanks as always
km
The syntax for Oracle's INSTR function is
instr (string1, string2, [start_position], [nth_appearance])
string1 is the string to search.
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)
Do anybody know the solution for this ????
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 :
=Iif(InStr(First(Fields!Ville.Value, "MW1R017A_IdentificationTravailleur_AdresseTravailleur"), "(") > 0,
Left(Split(First(Fields!Ville.Value, "MW1R017A_IdentificationTravailleur_AdresseTravailleur"), "(")(1), len(Split(First(Fields!Ville.Value, "MW1R017A_IdentificationTravailleur_AdresseTravailleur"), "(")(1)) - 1)
, First(Fields!Ville.Value, "MW1R017A_IdentificationTravailleur_AdresseTravailleur"))
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.
Hi experts;
I have a problem with unicode character 0x2300
I created this table
create table testunicode (Bez nchar(128))
Insert Data
insert into testunicode (Bez)values('Œ€„¢')
with 2 Unicode characters
Œ€ = 0x2300
„¢ = 0x2122
Selecting the data
select Bez from testunicode
I see
"?„¢"
„¢ = 0x2122 is ok but instead of 0x2300 there is 0x3f
When I modify the insert statement like that ( 8960 = 0x2300 )
insert into testunicode (Bez)values(NCHAR(8960)+'„¢')
and select again voila i see
"Œ€„¢"
Does anyone have an idea?
Thanks
I am trying to 'load' a copy of a SQLServer 2000 database to SQLServer 2005 Express (on another host). The copy was provided by someone else - it came to me as a MDF file only, no LDF file.
I have tried to Attach the database and it fails with a failure to load the LDF. Is there any way to bypass this issue without the LDF or do I have to have that?
The provider of the database says I can create a new database and just point to the MDF as the data source but I can't seem to find a way to do that? I am using SQL Server Management Studio Express.
Thanks!!
I'm chasing after a documetn that was available on one of the Microsoftwebsites that was titled somethign like "MS SQL Server Best Practices"and detailed a nyumber of best practices about securing the server.Included in this was revoking public access to the system tableobjects.Can someone post the URL where I can pick this up, or drop me a note oncontacting them for a copy of the document?
View 2 Replies View RelatedI have an app that uses a sqlserver 2000 jdbc driver to connect to a sqlserver 2000.
Is it possible to do a direct replacement of sqlserver 2000 with sqlserver 2005 express just by reconfiguring the app to point to the express? The app would still be using the sqlserver 2000 jdbc driver to try and make the connection.
If that is a possibility, what can be some differences in the configuration? Previously with 2000 the config information I entered is:
server name: "machinename"( or ip). I've also tried "machiname/SQLEXPRESS"
DB name: name of db instance
port: 1433(default)
user and pass.
My attempts so far results in
"java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC]Error establishing socket."
and
"java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC]Unable to connect. Invalid URL."
Hello,
I have an SQLServer Mobile database, and I would like to know if there is a way to upgrade it to SQLServer 2005 (.mdf) database. My database has no records in it, just the structure (tables etc). What I am actually asking is if I can create automatically a new SQLServer 2005 Database with the same structure as my existin SQLSErver Mobile database
Thanks in advance,
TassosTS
Hello people.
I am in the process of planning a server upgrade to sql2005 x64.
I created 2 linked servers: one to a SQL2000 sp4 server and one to a SQL7.0 SP3.
I have the following error when I query the linked servers.
OLE DB provider "SQLNCLI" for linked server "IVDM2K" returned message "Unspecified error".
OLE DB provider "SQLNCLI" for linked server "IVDM2K" returned message "The stored procedure required to complete this operation could not be found on the server. Please contact your system administrator.".
Msg 7311, Level 16, State 2, Line 1
Cannot obtain the schema rowset "DBSCHEMA_TABLES_INFO" for OLE DB provider "SQLNCLI" for linked server "IVDM2K". The provider supports the interface, but returns a failure code when it is used.
I am aware of KB 906954.
http://support.microsoft.com/default.aspx?scid=kb;en-us;906954
I applied the instcat.sql on the SQL2000SP4 server and my linked server issues for that one are gone.
However, I ran the instcat.sql script on the SQL7.0 sp3 server and the linked server is still giving me an issue.
Can someone help me find a solution to this?
Regarding KB935356, is there a "post" service pack 2 update for SQLServer Express?
Thanks.
Hello Expert!
I have 2 Database €“ Access & SQLServer(ver 7)
I need to Import Data TblShift from Access to SQLServer €“ using DTS I€™ve done this successfully!
Now I want to use parameter so I only importing record within range (e.g. ShiftDate BETWEEN 05-24-2006 AND 06-23-2006)
In SQLServer, I have created table to store the date range as following:
TblParameter
DateFrom: 04/24/2006
DateTo: 05/23/2006
How do I use the date range from TblParameter(SQLServer) to import record from TblShift(Access) using DTS?
Is this possible or any better solution for this?
TIA
Regards,
Hi Friends,
Can some please let me know the differences between sqlserver 2000 and sqlserver 7.0
Hi,
I am new to SQL Server 2005. I tried connecting to my local machine by using my machine name as Server name and then tried running the following query:
SELECT * FROM SYS.Objects. It gives me following error:
Invalid object name 'sys.objects'.
Whereas, if I connect to my local machine using mahcinenameSQLEXPRESS, then the above mentioned query runs fine.
Why is this difference? What is the difference when I login in these 2 different ways.
Any help would be appreciated.
Thanks in advance.
Any help
Is there a way to transfer data from a SqlServer db to a SqlServer Express db. I tried to use the backup file of SqlServer, but this file is not valid for SqlServer Express. Or there any alternatives?
thanks,
Henk
I have this function in access I need to be able to use in ms sql. Having problems trying to get it to work. The function gets rid of the leading zeros if the field being past dosn't have any non number characters.For example:TrimZero("000000001023") > "1023"TrimZero("E1025") > "E1025"TrimZero("000000021021") > "21021"TrimZero("R5545") > "R5545"Here is the function that works in access:Public Function TrimZero(strField As Variant) As String Dim strReturn As String If IsNull(strField) = True Then strReturn = "" Else strReturn = strField Do While Left(strReturn, 1) = "0" strReturn = Mid(strReturn, 2) Loop End If TrimZero = strReturnEnd Function
View 3 Replies View Related