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


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

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

MSQL Update

May 11, 2004

Can anyone help me with the MSSQL following problem:
I want to update the field "Date" with today´s date in a Tabel named Customers
I want to click on a "button" that runs something like:

Update Date with Today´s date where CustomerName = TextBox.text

Thanks in advance for your help

View 1 Replies View Related

Tools For MSQL

Nov 16, 2006


1. I am wondering if exist a tool to let me easily document my databases, unde MSQL.
e.g.I want to create a doc file or better a html file with all my databases objects, descriptions, DDLs etc...

2. Another tool to create code patterns; I need a something that generate custom code patterns, but in VBScript or C#.

p.s. Sorry if i created this topic on another forum.


<u><b>Jack Bower</b></u>
software developer
IATAR Studio

View 13 Replies View Related

MSQL Views And Arguments

Jul 26, 2004

Greetings everyone.

A couple days ago, i posted a question on the Access boards.Here (

Long story short, instead of filtering the results Locally, returned by query run locally on linked tables in an access front end, seems we should filter remotly. Seems easier and more secure.

We still want to hand out MS Access front ends. The results of queries run by users must be filtered based on authetication and values in the tables they query. There would be two ways to accomplish this.

1 - Stored procedures, apprearing as queries in Access.

2 - Views, appearing as tables.

Stored procedures are sure to work, but somewhat a hassle, sine we have to write code for each.

Views seem to be the simplest approach. we only need to build an SQL statement representing the new table. yet again, we have not done any constructive filtering. Is there a way to pass arguments to views just like stored procedures?

An exemple could be :

SELECT field1, field2, field3 FROM edu_details WHERE ID_department = @usr_dept AND ID_Division = $usr_div

usr_dept and usr_div being fields in the queried table(or course) AND auth information.



View 1 Replies View Related

Regarding MSQL Errorno: -2147467259

Dec 17, 2007

Hello friends,

i am newly installed my client server from there i suppose to access the application.... when i clicked on the application icon it showing he following message:

Please note down the Error and Call EDP!!!!
Error number->[-2147467259]
Error Text->[[DBNETLIB][ConnectionOpen(Connect()),]SQL Server does not exit or access denied,]

am getting the above error..... can any one through your view plz.........

ENV: windows 2003 small business server, MSSQL (server)
windowsXP (client)

its working fine with all other client mechines.........

View 1 Replies View Related

Creating Database And Table On MS MSQL Server

Jul 20, 2005

Hi All,I want to use MS sql server edition 2000. I have installed the server. Now iwant to create the database on this server with tables and triggers. Canplease someone suggest me how to do this ?Thanks a lot,Dinesh

View 1 Replies View Related

I Can Not Install MSQL Server 20005 TCP Express April Edition

Aug 10, 2005

hello all during the instaletion i get an error message that SQL server can not start the service, after that the programm rolls instalation someone knows what's going on?i use win xp pro.thank u for ur help.

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

Query To Retrieve Max Price Without Using Max Function And Sub Query

Mar 5, 2014



the above is the existing table and i need a query to retrieve max price with out using max function and sub query

View 6 Replies View Related

Query Function

Jan 16, 2008

Has anyone ever written an SQL (Select, etc.) function that could be placed in the App_Code folder of a project? I have a few web forms that have a couple dozen queries and I'm trying to build a good function to reduce clutter. The function I made (below) is in the App_Code folder and can be used by doing: Dim dr As SqlDataReader = GlobalFunctions.BuildSQLSelect("blah", "blah") in any one of my pages.
Public Shared Function BuildSQLSelect(ByVal ConnectionStringType As String, ByVal QueryString As String)   Dim ConnectionString As String = Web.Compilation.ConnectionStringsExpressionBuilder.GetConnectionString(ConnectionStringType)   Dim Connection As New SqlConnection(ConnectionString)   Dim Command As New SqlCommand(QueryString, Connection)   Command.Connection.Open()      Return Command.ExecuteReader()End Function
It works fine, but has one major flaw that prevents me from using it. I can't (at least I don't think I can) call Command.Connection.Close() once Return is hit and the function exits (especially since I still need to work with the DataReader).
Does anyone know of a better solution or know how to fix mine so I don't have tons of open connections floating around? Thanks!

View 2 Replies View Related

Function Vs. Sub-Query

Oct 19, 2004

When my sproc selects a function (which in itself has a select statement to gather data) it takes substantially longer time (minutes) than if I replace the function with a sub query in the sproc (split second). What is the reason for this?


View 2 Replies View Related

To Use Function In A Query

Feb 15, 2006

i hve select query where i display many columns with many conditions from 4 tables. in displaying using 2 column outputs i need to do calculations and on one another and display. so i wrote scalar function. but calling function is not possible to retrive all columns and insert into query.. how to do this .. help me in suggesting..

View 14 Replies View Related

Function Query

May 8, 2008

i have function:

can anybody help me to figure this issue? how can i get that.
Thank you.

View 2 Replies View Related

Query With Function Won't Run

May 29, 2008


I get the following error when trying to run this query in reporting services, but it executes perfectly in Management Studio, all I did was copy and paste:

TITLE: Microsoft Report Designer

An error occurred while executing the query.
Incorrect syntax near '.'.


Incorrect syntax near '.'. (Microsoft SQL Server, Error: 102)

For help, click:

SELECT dv.product ,
dv.itemname ,
dv.shorted ,
dv.onhand ,
dv.po_num ,
(SELECT t3.product ,
t7.itemname ,
t3.shorted ,
t4.onhand ,
t6.cardname AS t6_cardname,
WHEN t8.linestatus = 'O'
THEN t9.docnum
END) po_num
(SELECT t0.product product ,
WHEN t0.qty_topick <> t0.qty_picked
THEN t0.qty_topick - t0.qty_picked
END) shorted
FROM rbeacon.dbo.shipline2 t0
INNER JOIN rbeacon.dbo.shiphist t1
ON t0.packslip = t1.packslip
WHERE CONVERT(VARCHAR(8),t1.date_upld,3) = @Date
GROUP BY t0.product
) t3
INNER JOIN comparison.dbo.vlgxplc t2
ON t2.itemcode = t3.product COLLATE Latin1_General_CI_AS
(SELECT t0.product AS product,
SUM(t0.quantity) AS onhand
FROM rbeacon.dbo.binlocat t0
GROUP BY t0.product
) t4
ON t3.product = t4.product
INNER JOIN wbau.dbo.oitm t5
ON t3.product = t5.itemcode COLLATE SQL_Latin1_General_CP850_CI_AS
LEFT JOIN wbau.dbo.ocrd t6
ON t5.cardcode = t6.cardcode
INNER JOIN wbau.dbo.oitm t7
ON t3.product = t7.itemcode COLLATE SQL_Latin1_General_CP850_CI_AS
LEFT JOIN wbau.dbo.por1 t8
ON t3.product = t8.itemcode COLLATE SQL_Latin1_General_CP850_CI_AS
LEFT JOIN wbau.dbo.opor t9
ON t8.docentry = t9.docentry
WHERE t3.shorted <> 0
GROUP BY t3.product ,
t7.itemname ,
t3.shorted ,
t4.onhand ,
) dv

OUTER APPLY comparison.dbo.podatetest(dv.po_num) AS t10

GROUP BY dv.product ,
dv.itemname ,
dv.u_vlgx_plc ,
dv.shorted ,
dv.onhand ,
dv.po_num ,
ORDER BY dv.u_vlgx_plc,

I've worked out that it doesn't like me passing dv.po_num through the table valued function. If I change this to a static value, rather than the result of the case statement further up, reporting services will run the query.

Any idea how I can fix this? Thanks!

View 2 Replies View Related

Aggregate Function In Sub-query

Jan 15, 2014

I am trying to use the following syntax and it is saying I can't use an aggregate function in a subquery. I can't use a GROUP BY in this case because if another field in the project table (such as status) is different, that project will show up twice.So in this case I am using this syntax to show the most recent quote within the project.


My goal here is to show the most recent quote within each project (there can be multiple revisions of a quote within each project). I want to show other fields such as the status of the quote, but if the status is different between quotes, the GROUP BY on that field will cause it to be listed more than once. All I want to show is the most recent quote for each project.

View 3 Replies View Related

Add Count Function To My Query

Dec 17, 2007


I have created the following query... and need to get
the total records display for my report. I have tried
adding in the count(*) function to my select list, but I get
errors. Any help is appreciated.


FROM dbo.Subject A
INNER JOIN dbo.Incident B ON A.SubjectId = B.SubjectId

WHERE A.ParentSubjectName LIKE 'ACDelco Products%'

AND (B.CreatedOn >= '2007-01-01' AND B.CreatedOn <= '2007-11-30')

AND A.SubjectId IN

ORDER BY B.CreatedOn[/blue]

View 3 Replies View Related

Joining Function - Query

Feb 27, 2008

CREATE FUNCTION dbo.fn_copdmailinglist(@list_ varchar(60))
RETURNS @copdmailinglist TABLE
list_ varchar(60) ,
title_ varchar(255) ,
desc_ varchar(255),
message_id int ,
txt varchar(255) ,
cnt int ,
cnt_txt varchar(255)

--Returns a result set that lists all the copds
WITH ListManager.dbo.[List Copd](list_ , title_ , message_id , txt , cnt , cnt_txt ) AS
(select @list_ , gmc.name_, osc.message_id , txt , cnt , cnt_txt from ListManager.dbo.[Open statisticscopd]('') osc
left outer join ListManager.dbo.get_mailingidcopd_('') gmc
on gmc.name_ = osc.title_
where list_ = @list_

-- copy the required columns to the result of the function
INSERT @copdmailinglist
SELECT list_ , title_ , message_id , txt , cnt , cnt_txt
FROM ListManager.dbo.[List Copd]

i m getting error that Incorrect syntax near the keyword 'WITH'.

can anyone tell me how to join functions in sql?


View 4 Replies View Related

Count/sum Function Query

Mar 14, 2008

create function mytotalcount

(@audit varchar(50), @startdate datetime, @enddate datetime)

returns table




select t.value,sum(t.countvalue) as totalcount from


sm.value,count(sm.value) as countvalue

from subjectbase s
join stringmap sm
on s.organizationid = sm.organizationid

inner join audit a
on s.subjectid=a.subjectid

inner join incidentbase i
on i.subjectid=s.subjectid

where a.auditid= @audit and (i.modifiedon between @startdate and @enddate) and
sm.attributename = 'contractservicelevelcode' and
sm.ObjectTypeCode = 112

group by sm.value

) t

group by t.value


value totalcount
NHLBI Employee329
NIH Employee329

instead of different values i m getting same...
there is something wrong in joins..can anyone help me?


View 2 Replies View Related

Median Query Without Using Function

Feb 24, 2006

I have a table (cars) with 3 fields:VIN, Class, sell_price101, sports, 10000102, sports, 11000103, luxury, 9000104, sports, 11000105, sports, 11000106, luxury, 5000107, sports, 11000108, sports, 11000109, luxury, 9000i need to write a query that WITHOUT USING A FUNCTION will return themedian selling price for each class of car. result should look like:Class, Med_Priceluxury, 9000sports, 11000thanks to all u SQLers

View 4 Replies View Related

Query On Datediff Function

May 18, 2007


I am trying to use DataDiff function and I have used the following queries:


select datediff(ms, '2007-05-18 19:35:07.320','2007-05-18 19:35:07.320') as test

Expected Result: 0 milliseconds

Actual Result: 0 milliseconds


select datediff(ms, '2007-05-18 19:35:07.320','2007-05-18 19:35:07.321') as test

Expected Result: 1 milliseconds

Actual Result: 0 milliseconds


select datediff(ms, '2007-05-18 19:35:07.320','2007-05-18 19:35:07.322') as test

Expected Result: 2 milliseconds

Actual Result: 3 milliseconds


select datediff(ms, '2007-05-18 19:35:07.320','2007-05-18 19:35:07.323') as test

Expected Result: 3 milliseconds

Actual Result: 3 milliseconds


select datediff(ms, '2007-05-18 19:35:07.320','2007-05-18 19:35:07.324') as test

Expected Result: 4 milliseconds

Actual Result: 3 milliseconds


select datediff(ms, '2007-05-18 19:35:07.320','2007-05-18 19:35:07.325') as test

Expected Result: 5 milliseconds

Actual Result: 6 milliseconds


select datediff(ms, '2007-05-18 19:35:07.320','2007-05-18 19:35:07.326') as test

Expected Result: 6 milliseconds

Actual Result: 6 milliseconds


select datediff(ms, '2007-05-18 19:35:07.320','2007-05-18 19:35:07.327') as test

Expected Result: 7 milliseconds

Actual Result: 6 milliseconds


select datediff(ms, '2007-05-18 19:35:07.320','2007-05-18 19:35:07.328') as test

Expected Result: 8 milliseconds

Actual Result: 6 milliseconds


select datediff(ms, '2007-05-18 19:35:07.320','2007-05-18 19:35:07.329') as test

Expected Result: 9 milliseconds

Actual Result: 10 milliseconds

Does any one know, why datediff does not return the Expected Result? There does not seem to be any consistency.



View 1 Replies View Related

Function Help: Parameter Query

Apr 26, 2006

What SQL Function Criteria string replaces [forms]![myForm].[myField]?

I have a function that I want to pass criteria to from a drop down list. I tried using the same Access string in the Function but it does not work.

View 1 Replies View Related

Copyrights 2005-15, All rights reserved