The Following Prints Out 0 - SQL Procedure And Use Of The Charindex Function.
Jul 20, 2005My code prints out 0 for the value of charindex ( 'Hello.', '.' ).
Did I miss something? I think it should print out 6!
ADVERTISEMENT
Transact SQL :: Can Add 1 To Charindex Function But Can't Minus 1
Aug 25, 2015The charindex can run this
LEFT([Description], CHARINDEX('(', [Description]) + 1) Â as NewDesc,
But I can't run
LEFT([Description], CHARINDEX('(', [Description]) - 1) Â as NewDesc,
Help: About Charindex Function Doesn't Work With Variable
May 23, 2007Hello to all,
I hope that somebody can help me.
I have written a sql query to search Partner. I have a wtcomValidRelationships Table. There are two Fields (IDMember(type: Int) and RelationshipIDs(type: varchar(1000)) in this table.
Example: 3418 has 3422 RelationshipID and 3422 has 4088 RelationshipID, if i want to check if there is a relationship between 3418 and 4088.
declare @IDM int;
declare @IDO char(100);
set @IDM = 3418;
set @IDO = '4088';
select B.IDMember
from wtcomValidRelationships as A, wtcomValidRelationships as B
where A.IDMember = @IDM and charindex(cast(B.IDMember as char(100)),A.RelationshipIDS) > 0
and charindex(@IDO,B.RelationshipIDs) > 0
Using this query i get nothing.
I try to use constant in charindex and i get result.
declare @IDM int;
declare @IDO char(100);
set @IDM = 3418;
set @IDO = '4088';
select B.IDMember
from wtcomValidRelationships as A, wtcomValidRelationships as B
where A.IDMember = @IDM and charindex('3422',A.RelationshipIDS) > 0
and charindex('4088',B.RelationshipIDs) > 0
So i think that charindex doesn't work with variable. But I must use variable. Can someone help me? What should i do ?
Thanks
Best Regards
Pinsha
SQL Server 2008 :: Left Function And CHARINDEX
Mar 30, 2015SELECT LEFT(InstanceName,CHARINDEX(' ',InstanceName)-1)
FROM ServerDB
Returns the following error.
Invalid length parameter passed to the SUBSTRING function.
String Index Function (substring / Charindex) - Extract Specific Characters From Data
Aug 5, 2013SQL Query. What i need is to be able to extract specific characters from data.
Eg name
1.2-KPIA1-App-00001 this is a name i require, but i also require the '1.2' and the 'KPIA1' to be displayed in new columns in the results
i.e. 1.2-KPIA1-App-00001 1.2 KPIA1
*I need this in part of a script as there is thousands of rows of data.
Some PRINTs Won't Print Anything
Oct 20, 2006I'm trying to use the PRINT statement to trace what an SP is doing, while
running in QA.
However, some PRINTs seem not to print anything. I can see that the
code part is being executed.
Any ideas? (It's just simple prints like print 'executing an insert' .)
C0 Format Prints R Instead Of $
Apr 29, 2008
In my SQL2005 SSRS table, I have formatted the numbers to be C0 (currency, 0 decimal places). The numbers display like this: Even the fomat expression builder shows an R in its example!
R 456,220
In the same report, a table immediately above it that I created yesterday is formatted the same way but displays with the correct formatting:
$456,220
What did I do? Thanks!
RS Prints Only The First Page
Jan 26, 2007Hi,
I have several reports out of which only one report is acting weirdly.
The report displays fine when I open it up but when I try to print it,
it only prints the first page. I tried exporting it to PDF and the same
thing, the PDF has only one page. I tried exporting in all formats and
found that PDF and TIF are not working but the other formats (web
archive, HTML, XML and CSV) are fine. The excel format gives me this
error:
Exception of type
Microsoft.ReportingServices.ReportRendering.ReportRenderingException
was thrown. (rrRenderingError) Get Online Help
Exception of type
Microsoft.ReportingServices.ReportRendering.ReportRenderingException
was thrown.
Index was out of range. Must be non-negative and less than the size of
the collection. Parameter name: index
I need the PDF format to work. Any ideas why this particular report is
bad? Another note of interest. This report uses only a subreport that works
fine in another report. Please help.
Thanks,
Anand
Prints Everything Big In Direct Printing
Apr 10, 2007
I am using SQL RS 2005 with VS2005 in Windows Application. I have placed Report Viewer on Windows From and then report is attached with it. Now report previews well in BIDS as well as in VS2005. Moreover it exports perfectly to pdf and excel but when I click print button on report viewer and give direct print to printer, font size, textbox, rectangle and every other thing become large, like instead of 10 font size, it prints 12, instead of 240 width of rectangle, it prints 280 or so.
There must be some solution to this thing. Some one help
Reports Prints 1 Page Only
Jul 11, 2007I am getting an error "An error occured during printing (0x80004005)" - when i am trying to print - and the i still am able to print - but only 1 page.
Thre registry key - Reporting services was not there - so i created it at
[HKEY_CURRENT_USERSoftwareMicrosoftMicrosoft SQL Server80Reporting Services]
"LogRSClientPrintInfo"=dword:00000001
Please help - what should i do now ?
Mysterious Gaps On PDFs And Prints
Aug 8, 2007Hello, I have a report that looks fine in preview, but when put to PDF or printed contains gaps at the right and bottom.
Here is a picture of the problem: http://northeasttigers.webng.com/pdfproblem.jpg
My tables are the same width as the body, 15.5cm, and the report width is 21cm. Also adding the bottom table's top location attribute to it's height gives the same height as the body.
Any help much appreciated.
Greg
PDF Appears In Landscape But Prints In Portrait
Apr 28, 2008All of my reports that are formatted 11x8.5 are outputting to PDF correctly. When I open the PDF in Adobe Acrobat, it appears in Landscape on the screen. However, when I click the print button, by default it is printing it in portrait. I can go to File-->Print Setup and change the orientation from Portrait to Landscape, but why doesn't it translate from the file orientation? This may just be a bug in the Adobe Acrobat software, but I want to make sure there isn't something I can do.
Thanks.
SSRS 2005 Report Prints Blank Pages
Nov 12, 2007
I am very new to Reporting Services. When I set the report to landscape, either by setting width and height at 11 X 8.5 when in layout, or by setting landscape in page setup when I am in preview, a blank page prints in between each report page. I've checked the forum, and tried setting margins to 0, but have not had any success. I saw this response:
the body width plus margins should be less than the defined page width. but I'm not sure what settings are being referred to
Has anyone been able to solve this problem?
Thanks very much.
Sue
SqlDataSource.SelectParameters Causing Procedure Or Function Stored Procedure Has Too Many Arguments Specified.
Sep 12, 2006 Hi everybody, I am having trouble how to fixed this code. I am trying to supply the parameterinside a stored procedure with a value, and displays error message shown below. If I did not supply the parameter with a value, it works. How to fix this?Error Message:Procedure or function <stored proc name> has too many arguments specified.Thanks,den2005
Stored procedure:
Alter PROCEDURE [dbo].[sp_GetIdeaByCategory]
@CatId <span class="kwd">int</span> = 0
AS
BEGIN
SET NOCOUNT ON;
Select I.*, C.*, U.* From Idea I inner join IdeaCategory C on I.CategoryID = C.IdeaCategoryID inner join Users U on I.UserID = U.UserID Where I.CategoryID = @CatId Order By LastModifiedDate Desc
End
oDataSource.ConnectionString = constr;
oDataSource.SelectCommand = storedProc;<span class="cmt">//storedproc - sp_GetIdeaByCategory</span>
oDataSource.SelectCommandType = SqlDataSourceCommandType.StoredProcedure;
oDataSource.SelectParameters.Add(<span class="st">"@CatId"</span>, catId);
gdvCategories.DataSourceID = oDataSource.ID;
gdvCategories.DataBind(); <<--- Error occured here
Gridview / SqlDataSource Error - Procedure Or Function &<stored Procedure Name&> Has Too Many Arguments Specified.
Jan 19, 2007Can someone help me with this issue? I am trying to update a record using a sp. The db table has an identity column. I seem to have set up everything correctly for Gridview and SqlDataSource but have no clue where my additional, phanton arguments are being generated. If I specify a custom statement rather than the stored procedure in the Data Source configuration wizard I have no problem. But if I use a stored procedure I keep getting the error "Procedure or function <sp name> has too many arguments specified." But thing is, I didn't specify too many parameters, I specified exactly the number of parameters there are. I read through some posts and saw that the gridview datakey fields are automatically passed as parameters, but when I eliminate the ID parameter from the sp, from the SqlDataSource parameters list, or from both (ID is the datakey field for the gridview) and pray that .net somehow knows which record to update -- I still get the error. I'd like a simple solution, please, as I'm really new to this. What is wrong with this picture? Thank you very much for any light you can shed on this.
View 9 Replies View RelatedROW_NUMBER() Function Is Not Recognized In Store Procedure.(how To Add ROW_NUMBER() Function Into SQL SERVER 2005 DataBase Library )
Feb 4, 2008Can anybody know ,how can we add builtin functions(ROW_NUMBER()) of Sql Server 2005 into database library.
I get this error when i used into storeprocedure :
ROW_NUMBER() function is not recognized in store procedure.
i used MS SQL SERVER 2005 , so i think "ROW_FUNCTION()" is not in MS SQL SERVER 2005 database library.
I need to add that function into MS SQL SERVER 2005 database library.
Can anbody know how we can add that function into MS SQL SERVER 2005 database library?
Procedure Or Function 'stored Procedure Name' Expects Parameter Which Was Not Supplied
Mar 26, 2007Has anyone encountered this before?
Procedure or Function 'stored procedure name' expects parameter '@parameter', which was not supplied.
It seems that my code is not passing the parameter to the stored procedure.
When I click this hyperlink:
<asp:HyperLink
ID="HyperLink1"
Runat="server"
NavigateUrl='<%# "../Division.aspx?CountryID=" + Eval("CountryID")%>'
Text='<%# Eval("Name") %>'
ToolTip='<%# Eval("Description") %>'
CssClass='<%# Eval("CountryID").ToString() == Request.QueryString["CountryID"] ? "CountrySelected" : "CountryUnselected" %>'>
</asp:HyperLink>
it is suppose to get the country name and description, based on the country id.
I am passing the country id like this.
protected void Page_Load(object sender, EventArgs e)
{
PopulateControls();
}
private void PopulateControls()
{
string countryId = Request.QueryString["CountryID"];
if (countryId != null)
{
CountryDetails cd = DivisionAccess.GetCountryDetails(countryId);
divisionNameLabel.Text = cd.Name;
divisionDescriptionLabel.Text = cd.Description;
}
}
To my app code like this:
public struct CountryDetails
{
public string Name;
public string Description;
}
public static class DivisionAccess
{
static DivisionAccess()
public static DataTable GetCountry()
{
DbCommand comm = GenericDataAccess.CreateCommand();
comm.CommandText = "GetCountry";
return GenericDataAccess.ExecuteSelectCommand(comm);
}
public static CountryDetails GetCountryDetails(string cId)
{
DbCommand comm = GenericDataAccess.CreateCommand();
comm.CommandText = "GetCountryDetails";
DbParameter param = comm.CreateParameter();
param.ParameterName = "@CountryID";
param.Value = 2;
param.DbType = DbType.Int32;
comm.Parameters.Add(param);
DataTable table = GenericDataAccess.ExecuteSelectCommand(comm);
CountryDetails details = new CountryDetails();
if (table.Rows.Count > 0)
{
details.Name = table.Rows[0]["Name"].ToString();
details.Description = table.Rows[0]["Description"].ToString();
}
return details;
}
As you can see I have two stored procedures I am calling, one does not have a parameter and the other does. The getcountry stored procedure returns the list of countries in a menu that I can click to see the details of that country. That is where my problem is when I click the country name I get
Procedure or Function 'GetCountryDetails' expects parameter '@CountryID', which was not supplied
Someone please help!
Thanks Nickdel68
CHARINDEX
Jul 23, 2005SQL Server 2000Ya know, it is always the simplest stuff that gets ya !!I am having the hardest time getting a simple piece of code working.Must be brain dead today.Goal: Get the users full name from a stringHere is sample data:"LDAP://blahblahblah/CN=Kevin Jones,OU=DevEng,DC=nobody,DC=priv,DC=com"Code:IF LEN(@strReturnValue) > 0 BEGINSELECT @strReturnValue = SUBSTRING(@strReturnValue,(CHARINDEX('CN=',@strReturnValue)+3),(CHARINDEX(',',@strReturnValue)-1))ENDIt will extract:"Kevin Jones,OU=DevEng,DC=nobody,DC=priv,DC=com"I want it to extract:Kevin JonesThanks.
View 3 Replies View RelatedUsing CHARINDEX Vs LIKE In WHERE?
Jul 20, 2005Hi All,Just wondering if it's any more efficient to use the following SQLstatement to return a partial match from a column as opposed to thesecond statement.SELECT * FROM Table1 WHERE CHARINDEX('value', mycol) > 0Versus:SELECT * FROM Table1 WHERE mycol LIKE '%value%'Does anyone have any thoughts on whether or not either of thesestatements would return a result quicker than the other?Many thanks in advance!Much warmth,Murray
View 5 Replies View RelatedSubstring, Charindex, ??
Feb 18, 2000I am looking for a way to return only the numbers from the following example:
Column1 varchar(255)
Column1
---------------------
10 rows copied.
55004 rows copied.
1000004 rows copied.
Once I get the numbers I will then convert (Cast) them as Int.
Thank you in advance,
tw
Charindex And Substring
May 26, 2005Hi All,
I have an address field that has data that needs to parsed. The data has different formats. I have tried charindex and substring but I am not getting the results I need.
Here is what the data looks like:
Paper Mill Rd 3332
Allegheny Ave 13 1/2
Balto Nat'l Pike 6600 R-2
Rolling Rd N 3030
Here is the results I am looking for:
street dir street number street name street type street unit
3332 Paper Mill Rd
13 1/2 Allegheny Ave
6600 Balto Nat'l Pike R-2
N 3030 Rolling Rd
Can you help? Thanks.
IN Clause And CharIndex
Jan 20, 2004Hi, The following stored procedure creates this error, "Syntax error converting the varchar value '17,18,405,408,406,493' to a column of data type int." I tried using a Convert and a Cast function but it still failed. I then read something about using the CharIndex function, but I can't figure it out. Any help would be much appreciated in getting this to work.
Thanks
CREATE PROCEDURE [ResourceProducts]
@ProductHtml varchar(200)
AS
SELECT Resources.ResourceId, Resources.Heading
FROM Resources
INNER JOIN Products
ON Resources.ResourceId IN (Products.Resources)
Where Products.ProductHtml = @ProductHtml
GROUP BY Resources.Heading, Resources.ResourceId
GO
How To Use IF Or Case With Charindex?
Dec 18, 2007Hi
I have a column col1 in a table having below strings. How do I get only the required part of the string as I explaind below. (Needed strings are followed by i.e as below).
txtString=duracell+alkaline+battery+&submit1=Search&OVR31&OVKWID=24137211531
i.e : duracell+alkaline+battery+
txtString=sealed+lead+acid+batteries+&submit&gclid=CMekn-fesZAodpzIcHA
i.e : sealed+lead+acid+batteries+
ID=3&Department=
i.e : 3
ID=22893
i.e : 22893
lngSessionId=837541253&pid=22714
i.e : 22714
CCcode=502&OrderId=INT10350&puramt=1773
i.e : INT10350
tduid=5c14526847651e9054552acc134e9a84&url=http://www.somedomain.co.uk/proddetails.asp?id=4204
i.e : 4204
Code I am using (Got this one from my other posting)
declare @QueryString nvarchar(255)
select@QueryString = 'ID=23456&Depatment='
selectsubstring(@QueryString, charindex('ID=', @QueryString) + 3,
charindex('&', @QueryString) - charindex('ID=', @QueryString) - 3) from Pageviews
1. But the above code only works if the string starts with 'ID='.
2. Because the col1 has strings starts with different letter this code fail to excute and gives error
3. Pathindex with regular express '(patindex('%[^0-9]%',v)-1)' cannot be used because it gets anynumber
in the string - string has mixer of numbers and letters
SO IF ANYONE LET ME KNOW HOW TO USE IF or CASE with 'charindex' or any other method to get the above said result would be greatfull.
CHARINDEX AND SUBSTRINB
Jan 3, 2008I have my column and the info is.
UserID=176&StoreID=210
sometimes there is more between the two so im tring to use a CHARINDEX and Substring
Substring(Parameters, CHARINDEX('UserID', Parameters),3)
When I do this it brings back just the 'USE' I need it to bring back the number associated with it. In this case the 176 how do i get that number?
CHARINDEX Issue
Mar 4, 2008I have used CHARINDEX successfully to search for a substring going from left to right in a string, but how do I search from the end of a string and go backwards?
Here is a sample of the problem I am trying to solve. Here is a list of cities, some with two names and some with more than two. I need a way to extract only those cities with two names. I wanted to do this by locating the first space in the string and the last space in the string. If the first space equals the last space, then the city should only be a two word city. Otherwise it is a three name city or greater. So what string functions can I use to locate the first and subsequent spaces in a string?
Here is the current version of my SQL statement:
SELECT
City.City,
CHARINDEX (' ' ,City.City) AS First_Space
FROM
City
WHERE
CHARINDEX (' ' ,City.City) <>0
AND HERE IS A SMALL SAMPLE OF WHAT IS RETURNED:
MARINE CITY7
MARINE ON SAINT CROIX7
MARION CENTER7
MARION HEIGHTS7
HIGH RIDGE5
HIGH ROLLS MOUNTAIN PARK5
HIGH SHOALS5
Thanks to all who understand and can offer usable advice in my dilemma.
Jason.
Charindex Question
Mar 21, 2006I have a field containing a string with '/' in it multiple times.How can I return the charindex of the last occurance of '/' in thestring?Regards,Ciarán
View 1 Replies View RelatedPROBLEM Using CharIndex(), IF, OR Etc- PLEASE HELP
May 11, 2007What is the best way to essentially use the charindex(find) functionif the value is could be more than one variable (A or B or C)I can't seem to get an "or", "if" or "select if" to workBelow is the code that works, I need 'GOV' to be either 'GOV' or 'FWD'or 'LIB'sqlstring = "SELECT Distinct substring([exposurename]," _& Len(Worksheets(4).Range("j5") & "_VAR_" _&Worksheets(4).Range("C4").Offset(Worksheets(4).Range("c3"), 0)) + 2 _& ",charindex('GOV',[exposurename])-" &Len(Worksheets(4).Range("j5") _& "_VAR_" &Worksheets(4).Range("C4").Offset(Worksheets(4).Range("c3"), 0)) + 3 _& ") AS Drivergrp2 " _& "FROM mars.dbo.mroInventoryProductGreeks_Latest" _& "Where producttype = 'creditdefaultswap' " _& "AND exposureName like '" &Worksheets(4).Range("j5") & "_VAR_" &Worksheets(4).Range("C4").Offset(Worksheets(4).Range("c3"), 0) & "%' "_Much appreciation if you can help
View 2 Replies View RelatedCHARINDEX And STUFF/REPLACE
Jul 23, 2005Hello,I need to be able to replace only the first occurance of a space characterin a column.Reason being is the data in the column I am trying to replace seems to haveumpteen space characters after each entry, so a simple replace functionwill replace all the spaces after it with what I want!I have thought of RTRIM to get rid of the spaces after and then replace, Ihave also thought of CHARINDEX to find the first occurance of a space andSTUFF to replace it.I have done my homework on these functions!But I am having trouble writing such a statement,I've never written a query which would use more then one function on onecolumn you see and I am getting confused!I'll tell you what I want to do in simple stepsReplace only the first found space in a name column, but then if a name hasa middle initial that will be a problem,Replace that with a dot.then concatanate '@emailaddress;@emailaddress2' after itso when SQLServer does the select it will bring back something likejoe.bloggs@emailaddress;emailaddressBut I guess I'd also need joe.n.bloggs@emailaddress;emailaddressThe data in the column looks like this at the momentjoe bloggsBut I guess there may come a time when we havejoe n bloggs, just to complicate things!What is your advice, and how do I write a query like thisI have been playing around with it in Query Analyser but as I said I amgetting confused and need some help if you don't mindThanks a lot to all who reply :-)RegardsJayne
View 2 Replies View RelatedSql Charindex Split String
Jul 23, 2005HelloI am quite hopeless and of course a newbe.The situation: Sql2k / queryI would like it ot break down the following string:2004 Inventory:Ex.Plant Farm1:1st Cut:Premium:0094Whereby:Year = '2004 Inventory'plant= 'Ex.Plant Farm1'cut = '1st Cut'grade = 'Premium'lot# = '0094'It is always seperate by ':', but it can be 5 fields or 4 or 3 and sooncode to create the view:CREATE VIEW dbo.TESTASSELECT FullName, LEFT(FullName, CHARINDEX(':', FullName + ':') -1) AS year, CASE WHEN LEN(FullName) - LEN(REPLACE(FullName, ':', ''))[color=blue]> 0 THEN LTRIM(SUBSTRING(FullName,[/color]CHARINDEX(':', FullName) + 1, CHARINDEX(':', FullName + ':',CHARINDEX(':', Fullname) + 1) - CHARINDEX(':',FullName) - 1)) ELSE NULL END AS Plant, CASEWHEN LEN(FullName) - LEN(REPLACE(FullName, ':', '')) > 1 THENLTRIM(SUBSTRING(FullName,CHARINDEX(':', FullName + ':', CHARINDEX(':',FullName) + 1) + 1, CHARINDEX(':', FullName + ':', CHARINDEX(':',Fullname) + 1) - CHARINDEX(':',FullName+':') - 1)) ELSE NULL END AS [Cut]FROM dbo.ItemInventoryCan anyone help me with this? I am stuck half the way and get for cutthe rest of the string: '1st Cut:Premium:0094'Thanks!
View 5 Replies View RelatedCharIndex, Left T-SQL Question
Feb 15, 2008Hello,I was hoping someone could help me with this SQL statement.The column 'options.optionDescrip' is a varchar field.Some values of the 'options.optionDescrip' contain commas, some donot.We are trying to evaluate against any data in the'options.optionDescrip' column located to the left of a comma (if acomma does exist, which it may not). If no comma exists, then we tryand evaluate against the entire field, not just the part to the leftof the comma.SELECT options_optionsGroups.idProduct FROM options_optionsGroupsJOIN Options ON options_optionsGroups.idOption=options.idOptionJOIN products ON options_optionsGroups.idProduct=products.idProduct WHERE (CASE WHEN CharIndex(',',options.optionDescrip) = 0 THENoptions.optionDescrip LIKE '" & gauge & "%' ELSELeft(options.optionDescrip,CharIndex(',',options.o ptionDescrip)) LIKE'" & gauge & "%' END)Thanks for any input you can provide, I appreciate it.
View 1 Replies View RelatedTransact SQL :: Converting A Value Using Charindex
Jul 10, 2015What is the best way to convert 3/1/1/12/0 into xxx-xx-xx-xx-xx format by appending zeros if required. final result should be
300/10/10/12/00.
Parse A String Using Charindex
Jan 15, 2008Hi,
I've the following query. I'm using the yellow highlighted to join 2 tables, as these tables dont have a relationship between them.
The format of the name field is 'AAAA-BBBBBB-123'
here A will be only 4 chars, followed by '-' B can be any number of chars again followed by '-' and the last is the id which I'm using to do a join. This query will fail if the id is just 1 digit as its taking the last 3 chars as the id.
I dont know how to get the id from the right using charindex. Charindex would search for the first occurence of '-' from the right and get the chars after hypen i.e 123. How can this be achieved?
SELECT id AS 'ID',
name AS 'name',
sequence AS 'num'
FROM
FirstTable A
INNER JOIN SecondTable q
ON (CONVERT(INT,RIGHT(name,3))= a.id)
INNER JOIN ThridTable t
ON(t.id = q.id)
INNER JOIN FourthTable s
ON (q.name = s.name )
WHERE A.id = @ID
AND t.name=LEFT(s.name,((CHARINDEX('-',s.name))-1))
ORDER BY 'ID','num'
One more question on this is: Is this a good way of joining tables? If I dont use this I've a very large query containing unions. Which one should be bug-free and more efficient?
Thanks,
Subha
CHARINDEX BUG Returns Wrong Value
Jan 8, 2008Hi
I've got a problem which looks like it might be a bug with the CHARINDEX function for Microsoft SQL Server.
The following line of code returns TRUE (1), but you would expect it to return FALSE (0):
SELECT CHARINDEX('Prausse', 'Prauße', 1)
Is there a way around this problem?
Thanks in advance for your responses.