String Function - InStr

Dec 11, 2002

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.

View 4 Replies


Instr Function

Oct 26, 2006

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 Related

Using Instr() Len() And Mid() Function In SQL Statement

Oct 11, 2004

I 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?


View 2 Replies View Related

I Am Looking For The Function That Is The Same As InStr In Access

Jan 29, 2001

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

View 1 Replies View Related


Jul 20, 2005

Hi everybody,I was looking for an equivalent ORACLE INSTR Function in MSSQL but Idont found it and I dont 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 Related

INSTR Function In TransactSQL Of SQLServer 7

Sep 25, 2000

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


I'm sorry for my bad English
Thank you for everything


View 2 Replies View Related

Instr (vbscript) Function Explanation ???

Jan 15, 2001

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

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

Any help would be great.


View 1 Replies View Related

Instr Function For MSQL Query

Jun 4, 2007

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?

View 1 Replies View Related

Find? Instr? Indexof?

Jan 20, 2006

is 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

View 1 Replies View Related

Using CURSOR For INSTR Purposes

Mar 21, 2007

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)


1___Opera+7_______Opera 7
2___Opera/9_______Opera 9
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.

View 1 Replies View Related

Instr() Equivalent In SQL Server

Jul 20, 2005

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

InStr To Remove : And , And Sum Data

May 1, 2007

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


View 4 Replies View Related

Equivalent Of Oracle's INSTR( With 4 Parameters) In SQL Server

Feb 2, 2005

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 ????

View 1 Replies View Related

Reporting Services :: Getting Error Displayed When Using InStr

Nov 18, 2015

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.

View 8 Replies View Related


May 14, 2001


Is there any fucntion avaible in SQL for search a character position in a string.

e.g. 'ABC-DEF'

- is located at the position of 4

I tried the charIndex, Is it same as Instr in ORACLE. Is there any INSTR function available

View 1 Replies View Related

A String Function

Aug 25, 1999

does anyone know a function to count the number of times a substring appears in a string?
example NumberOfTimes("i","mississippi") would return 4

View 3 Replies View Related

Right And String Function

Jun 21, 2001

I need to take the character from a text field. I need the character which
is the second one from the end(right). Like out of '12345' or '99821'
I would need the 4 from the first and the 2 from the second. Can I combine
a string and a right statement to do this?

View 4 Replies View Related

SQL String Function Help

Mar 13, 2006

Here is the scenario.

I have a 10 position varchar field that holds a project number. The project number is in the form yy-nnn... where yy is the current year (06) and nnn is a sequential number. The project number is displayed on a data entry form so that the next record has the next sequential number.

So when the data entry form first opens the project number field has 06-1 after that record is entered the form is cleared and reinitialized and the project number now shows 06-2. If the form is closed and then reopened the project number would still show 06-2 if that previous record was not entered and 06-3 if it was.

The way I am going about getting the next sequential number is the following:

SELECT MAX(RIGHT(ERFNumber, LEN(ERFNumber)-3)) AS MaxERFNumber From Table1

This approach is working fantastic until record 06-9 is entered. The above line does determine that 06-9 is the MAX record and then creates a record 06-10 but every subsequent time a record is attempted it still thinks 06-9 is the MAX value of the above statement.

I am assuming that since the field is defined as a varchar the MAX function is applying character logic versus numeric logic thus the 9 being treated as the MAX value.

Is there an SQL function that can change the result of RIGHT(ERFNumber, LEN(ERFNumber)-3) to a numeric value so that the MAX function will work correctly.

I know there are many other ways to do this but in the interest of time because the application is almost finished, changing this now is going to be a major undertaking.

Any help or guidence would be greatly appreciated.

View 5 Replies View Related

String Function

Jun 23, 2006

Hi all, iam trying to find a string function which would replace column value where there is a ssn with 1 and anything else(blank,null,...) with 0. i need to count the number of rows with ssn and one without ssn.
i checked few string functions but no use

any help appreciated

View 5 Replies View Related

SUM Function For String????

Aug 16, 2007

hi kristen or anybody there,

is there a way you can concat string columns in a select statement... i have two tables (joined) and i wanted to display a concatenated values.

data details:
table 1
pk_transactionid transactiondate and so on
1 1/1/2007 *********
2 1/2/2007 *********
3 1/3/2007 *********

table 2
pk_rowid fk_transactionid description
1 1 record1
2 2 record2
3 3 record3
4 1 record4
5 2 record5
6 1 record6

result set should be like this
pk_transactionid transactiondate description --> this is concatenated
1 1/1/2007 record1, record4, record6
2 1/2/2007 record2, record5
3 1/3/2007 record 6


SlayerS_`BoxeR` + [ReD]NaDa

View 12 Replies View Related

String Function

Dec 19, 2006

select distinct( as SDNFIn this select clause, I try to use the last 2 digit of year for y.yearvalue. which function should i apply to it?

View 1 Replies View Related

String Function

Jan 23, 2008

Is there a way to convert special characters such ö to o?


View 3 Replies View Related

String Function Needed

Sep 26, 2006

I have a table with a column called Userdef. I is a user defined field. It looks like this ;;Polk;D-0002;;;;As you can see it is delimited by semicolons.  I need to separate the semicolons into separate files like this  Field1Field2Field2Field4Field5Field6<null><null>Polk<null>D-0002<null>How do I write this query in SQL Server?  

View 1 Replies View Related

Is There AT-SQL Function Like VBScript STRING() ?

Aug 11, 2000

This function is useful for fixed width output. E.g.

String(expr,10-len(expr),'0') will give you a number with leading zeros so it is always 10 characters long.

Thanks in advance for your time.

View 1 Replies View Related

String Function Questions

Jan 10, 2001

Hello All,
I have am importing a file that contains an address field where the city and state are in the same field, IE ['Atlanta, GA']. How do I separate the city and state into separate fields. I can get the state by using the RIGHT function. My problem is getting the city information up to the comma.

Any help would be appreciated.


View 1 Replies View Related

SQL Search String Function

Jun 2, 2008

The 'LIKE' function looks for words that start with whatever is in the like condition. Is there an sql function similar but will look and compare at any part of the search string.

For example I am using a webservice in dot net to populate a dropdown list using this sql

SELECT compound_name FROM dbo.compound_name WHERE compound_name like @prefixText

In this table there is a compound called SILCAP310 and I would like the search function to pick up 310 if I put this into the @prefix parameter. (but I would still like the search to perform like the 'LIKE' does also.

SELECT compound_name FROM dbo.compound_name WHERE compound_name like @prefixText or compound_name SearchPartString @prefixText

Thanks in advance

View 4 Replies View Related

LEFT And String Function

Nov 3, 2006

select left('Hello World /Ok',charindex('/','Hello World /Ok')-1)Hello WorldThat works fine.However I got an error message:select left('Hello World Ok',charindex('/','Hello World Ok')-1)Instead of:'Hello World Ok'I get:Server: Msg 536, Level 16, State 3, Line 1Invalid length parameter passed to the substring function.Microsoft Doc incorrectly says:"LEFT ( character_expression , integer_expression )integer_expressionIs a positive whole number. If integer_expression is negative, a nullstring is returned."Is there an easier solutoin using left or any other string functioninstead of using a case statement?Also, charindex('/','Hello World Ok') should return NULL instead 0 sothat we can use isnull function.Thanks.

View 2 Replies View Related

Function To Parse A String

Mar 13, 2008

Hope someone can help... I need a function to parse a string using a beginning character parameter and an ending character parameter and extract what is between them. For example.....

Here is the sample string:

CREATE FUNCTION Parse(String, '-' , '.')

and it shoud return 2fdr4

View 8 Replies View Related

FINDSTRING String Function

Sep 21, 2007

I'm having issues trying to build an expression using the FINDSTRING function. Even when I use an example from BOL, I get an error upon clicking the "evaluate expression" button within expression builder saying "Cannot convert system.int32 to system.string".

Here is my test expression...

Code SnippetFINDSTRING("ABC", "A", 1)

Here is the BOL expression...
Code SnippetFINDSTRING("New York, NY, NY", "NY", 1) Both give me the same error message. I even tried casting it to an integer with no luck. Any ideas?
Code Snippet(DT_I4) FINDSTRING("ABC" , "A" , 1)

View 4 Replies View Related

MS SQL Function Return String - What Am I Doing Wrong?

Jun 6, 2005

Cannot see where I am going wrong. I always get a value of 0. I know my function works correctly, so it must be the VB.

CREATE FUNCTION [dbo].[getNextProjectID] ()
RETURNS varchar(10) AS
DECLARE @vNextProjectID varchar(10)
RETURN @vNextProjectID

Sub LoadNextProjectNumber()
        Dim vProjectID As String
        Dim cmd As New SqlClient.SqlCommand()
        cmd.Connection = sqlConn
        cmd.CommandText = "getNextProjectID"

        cmd.Parameters.Add("@vNextProjectID", vProjectID)
        cmd.Parameters("@vNextProjectID").Direction = ParameterDirection.ReturnValue

        vProjectID = cmd.Parameters("@vNextProjectID").Value

        txtProjectID.Text = vProjectID

End Sub

View 4 Replies View Related

Sql Server Function Returns Xml String

Oct 21, 2005

Hi, How to write a SQL function that returns a string that contains xml string from "SELECT ....FOR XML"In other word, I want to put result of select .. for xml into a variable.Thanks

View 3 Replies View Related

Write String Default Value Function

Jun 3, 2008

I have a datetime column and I set the Default value of the column to: getDate().

How can I set a nvarchar columns Default value to write string data (such as: "test")?

View 5 Replies View Related

ISNULL Function And String Concatenation

Jul 20, 2005

Dear GroupJust wondered how I can avoid the CHAR(32) to be inserted if @String1 is NULL?SET @String3 = ISNULL(@String1,'') + CHAR(32) + ISNULL(@String2,'')Thanks very much for your expertise and efforts!Best Regards,Martin

View 6 Replies View Related

Copyrights 2005-15, All rights reserved