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


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

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

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 View Related


Jul 20, 2005

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

Using IIF Function In An Sql Statement

Mar 12, 2004

i cant get this statement to run on sql server 2000.. does anyone know why?

SELECT FirstName, LastName, EmpNum, JobTitle, Discipline, City, IIf(State = 'FL', 'Florida', 'Some Other State') AS StateTest
FROM dbo.Employees

it works on MS Access. is there a new name for the IIF Function on sql server

View 1 Replies View Related

Custom Function In SQL Statement?

Mar 24, 2005

In MS Access, I could write a function in a module, then just call that function as part of the SQL statement. For example, "SELECT RemoveDashes([SS_No]) AS SSN FROM Employee" with "RemoveDashes" being the name of the function.

I'm trying to do the same with an page and sql server. I have a custom function in the code behind that I call in the SQL statement, but I get the error, "not a recognized function name".

What do I need to do to make this work?
All help is greatly appreciated!


View 7 Replies View Related

Calling A VB Function From An SQL Statement

Feb 19, 2003

I need to know if there's any way to call a VB function from within an SQl statement. We have text in Rich Text format in a database and need it converted to regular text before we are able to perform searches on the data. Maybe I can use a stored procedure to accomplish this conversion or to call a function that would do this? Any help would be appreciated.

View 2 Replies View Related

How To Use The Sum Function With No Group By Statement

Oct 26, 2012

I want to run a much larget SQL statement, but for examples sake this is a good starting point

Select efName, elName, eAddress, SUM(Convert(money, bonus1)+Convert(money, bonus2)+Convert(money, bonus3)) As TotalBonus, ePay FROM tableEInfo

It is telling me that I have to use Group By, but the problem is that most of my fields are text fields, which it looks like have to be converted in order to use with a group by statement. Is it possible to use the sum function with no group by statement?

View 13 Replies View Related

Select Statement In A Function

Jan 21, 2004

CREATE FUNCTION GetPerson (@SSN integer, @NamePrefix varchar(10), @FirstName varchar(30), @MiddleName varchar(30), @LastName varchar(40), @NameSuffix varchar(10), @HomeID integer, @MailID integer, @DOB timestamp, @Gender varchar(1), @MaritalStatus varchar(1))
RETURNS integer as
DECLARE @PersonID integer
set @PersonID=0
if @SSN>0 and @SSN<999999999
select Min(lngPersonID) AS PersonID from Persons where lngSSN=@SSN
if @SSN is not null
IF @LastName is not null and @FirstName is not null and @MiddleName is not null and @NamePrefix is not null and @NameSuffix is not null
select MIN(lngPersonID) AS PersonID from Persons
where strNamePrefix= @NamePrefix and strFirstName=@FirstName
and strMiddleName=@MiddleName and strLastName=@LastName
and strNameSuffix=@NameSuffix and lngSSN=@SSN
if @LastName is not null
select MIN(lngPersonID) as PersonID from Persons
where strLastName=@LastName
and lngSSN = @SSN
return (@personID)

I m having problem with the "Select" function
the error I m getting is
Select statements included within a function cannot return data to a client (error 444)

Cann I use "select" statement in the function? If not what is the alternative?

I reduced the size of the sproc because it is a big one, I donn have any proble with syntax.


View 10 Replies View Related

Aggregate Function In SQL Statement.

May 6, 2008


I want to count how many occurences their is of each date that is returned by my sql query. I am not sure how to add the aggregate function code to my query I know how to just tell it to count all records, but not to tell it to count for each group of dates. For example I want it to count how many times 5/6/08 shows up in the returned results and so on. Here is my query I currently have. Any help would be greatly appreciated! Thanks!

The enc_timestamp is my date field.

Select a.template_id, a.enc_timestamp, a.created_by, b.first_name, b.last_name, b.last_name +', ' + b.first_name as fullname
From template_audit a
Join user_mstr b on a.created_by = b.user_id
GROUP BY a.template_id, a.enc_timestamp, a.created_by,b.first_name, b.last_name
Having a.template_id IN (543,3172,3031,3030,3134,3135,3171,1401,1937,3985,3173,2320,57,849,1775,1400,1747,3695,3957,3750,3954,3027,3241)
ORDER BY a.enc_timestamp, b.first_name, b.last_name;

Thanks in advance,

View 17 Replies View Related

Delete Statement In Function

Jul 19, 2006

Is it possible to create a function that deletes records from a table ?

returns int
delete from ts_imported
return 0

This throws error like this:
Invalid use of side-effecting or time-dependent operator in 'DELETE' within a function.

View 6 Replies View Related

Replace Function In Sql Statement

Oct 15, 2007

how should i use replace function in sql statement??

in my scenario, user enters the meeting ID and i want that if user enters the meeting ID which includes apostrophe('), the stored procedure should remove the apostrophe before inserting it into database table.

my stored procedure accepts two parameters.
Is there any way to solve my problem??


View 4 Replies View Related

Function Used Twice In Select Statement

May 22, 2008


I've got a complex UDF I need to call twice in a select statement, as shown below:


dbo.myFunction(colName) * 2


The problem is, the result of "dbo.myFunction(colName)" is not being cached, so the function is executed again for "dbo.myFunction(colName) * 2". This is having a significant impact on performance (doubling the amount of time it takes for the query to execute).

Is there any way I can write the query so that dbo.myFunction is only executed once?

Thanks for your help,


View 5 Replies View Related

Function Or CASE Statement?

Feb 25, 2008


I have a Query some thing like this,

Select Table1.Code, 'field2' = CASE

WHEN 1 THEN (Select name From Table2 where Table2.Code=Table1.Code)
WHEN 2 THEN (Select name From Table3 where Table3.Code=Table1.Code)
WHEN 3 THEN (Select name From Table4 where Table4.Code=Table1.Code)

FROM Table1

Do I need to use Function instead of CASE for better performance ?



View 5 Replies View Related

Function Inside A Select Statement

Aug 23, 1999

Can I write a function inside a Select statement in sql server 7.0
If so HOW ?

Manish Mehta

View 2 Replies View Related

Invalid Insert Statement In The Function

Apr 26, 2005

Dear Friends.

I m trying to use the insert statement with in the function !
and i m getting this errror !

Server: Msg 443, Level 16, State 2, Procedure GetTotalCOst, Line 16
Invalid use of 'INSERT' within a function.

Please help me how to rectify it and how i can use the Insert statement with in the function !

Here is the code for the function.

create function dbo.GetTotalCOst(@varWork_no as numeric,@varSubWork_no as numeric)returns numeric as
Declare @valCost integer
Declare @TotService integer
Declare @TotParts integer
Declare @TotLabour integer
Declare @TotTravel integer
Declare @TotSubContract integer
select @TotService= isnull(sum(quantity*costprice),0) From SB_Service_Suppply_Details where work_no=@varWork_no and subwork_no=@varSubWork_no
select @TotParts= isnull(sum(quantity*costprice),0) From SB_PARTS_dETAILS where work_no=@varWork_no and subwork_no=@varSubWork_no
insert into dbo.SB_InvoiceCostingService values(@TotService,@TotParts,1,1,1,1,1,1)
return (@valCost)

View 2 Replies View Related

Use DateDiff Function Within Select Statement

Feb 3, 2015

I'm trying to use the DateDiff function within my select statement, but I'd like to add the parameter of greater than 30 days. This will have the query only return records where my bill stop date is greater than 30 days from the completion date. I currently have the datediff function within my select statement as

DATEDIFF (d,A.StopBillDate, a.CompletionDate) as [DIFFERENCE]

I would prefer to keep the datediff function within the select statement so as to have difference in days appear as a column within my output.I have been unable to add the parameter of > 30 days to the query without getting an error.

View 2 Replies View Related

Can I Use If Statement In A Table Valued Function?

Aug 22, 2007

I am using a function in sql server 2005 like this:
...... myfunction(... @FlagOn int)

if(@FlagOn = 1)
select * from.......
select * form....

But it keeps complaining there is some syntax error around if. What is it?


View 5 Replies View Related

Why The Function And Batch With Same Sql Statement Have Different Performance?

Jan 3, 2008

hi all,

I have a function and batch witch consisted of same sql statement, and they will get the same result. but time they take is different, and produce a little different query plans, another significant difference is the estimated numbers. the function is always slower than the batch.

does anyone know why same sql would produce different query plans performance? how do i can to let the function as fast as the batch?


View 6 Replies View Related

Aggregate Function For Select Statement Result?

Oct 19, 2004

Ok, for a bunch of cleanup that i am doing with one of my Portal Modules, i need to do some pretty wikid conversions from multi-view/stored procedure calls and put them in less spid calls.

currently, we have a web graph that is hitting the sql server some 60+ times with data queries, and lets just say, thats not good. so far i have every bit of data that i need in a pretty complex sql call, now there is only one thing left to do.

i need to call an aggregate count on the results of another aggregate function (sum) with a group by.

*ex: select count(select sum(Sales) from ActSales Group by SalesDate) from ActSales

This is seriously hurting me, because from everything i have tried, i keep getting an error at the second select in that statement. is there anotherway without using views or stored procedures to do this? i want to imbed this into my mega sql statement so i am only hitting the server up with one spid.

Tom Anderson
Software Engineer
Custom Business Solutions

View 3 Replies View Related

SQL Statement Performs Pivot Table Function?

Apr 13, 2006

I have the following data:Product Type Hours Controllers Development 105.0Controllers Research 1.0Controllers Sustaining 24.0How do I use SQL statement to change it to the following?Product Development Research SustainingControllers 105.0 1.0 24.0Thanks.DanYeung

View 2 Replies View Related

Can Insert Statement Works In Case When Function

Nov 5, 2007


Im beginer in sql,Please guide

can insert statement works fine in case when function

for example

case when condition1=true then (first insert statement based on some condition) when condition2=true then (second insert statement based on some other condition)

View 5 Replies View Related

Multi-statement Table-Valued Function

Oct 18, 2007

I'm creating a Multi-statement Table-Valued Function...

Is it possible to insert variables into the table? In other words, is it possible
to have something like

@value1 varchar(10)
@value2 varchar(10)

<do some work on value1 and value2>
INSERT @returningTable
@value1, @value2

instead of

<do some work on value1 and value2>
INSERT @returningTable
SELECT col1, col2 from T_SOURCE

Here's why I want to insert variables...My function needs to return a table which contains a 'partial' incremental key.
I'll go with an example to explain what i have to do

col1 col2
Mike 10
Mike 20
Ben 50
John 15
John 25
John 35

The table that my function needs to create should look like this
col1 col2 col3
Mike 10 1
Mike 20 2
Ben 50 1
John 15 1
John 25 2
John 35 3

I thought of creating a cursor and then looping through it generate col3 and save values of other individual columns in variables. But don't know how to use those variables when inserting records into function table.

Any other ideas? I'm caoming from Oracle world, I might be having some strange ideas on how to solve this problem. Any help is appreciated.

Thank you.

View 7 Replies View Related

Copyrights 2005-15, All rights reserved