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?
Hi,
I have Table called performance where i what to get the AsOfDate(char 10) from that table... and i want to get the most recent based on this function.
ALTER FUNCTION [dbo].[udf_Quarter] (@Date datetime)
RETURNS char(10)
AS
BEGIN
DECLARE @month int
SET @month = Month(@Date)
RETURN
CASE
WHEN @month BETWEEN 4 AND 6 THEN 5
WHEN @month BETWEEN 7 AND 10 THEN 6
WHEN @month BETWEEN 11 AND 12 THEN 7
WHEN @month BETWEEN 1 AND 3 THEN 8
END
END
and i want this to return 3/31/2008 but i want to make it dynamic like if it is "3/31' and year can be depending on the year it passes
Any help will be appreciated.
Thanks
Karen
Hello all. I have an Access table with EmpName, JobTask, and Hours. Multiple lines can contain the same Employee with the same JobTask. What I need to do is to list the Employee, JobTasks (grouped), and summed hours for each JobTask.
DB Ex:
John Doe Welding 8
John Doe Cleaning 4
Bubba Smith Fork Lift 3
John Doe Welding 7
Steve Johnson Welding 5
Bubba Smith Fork Lift 6
Page output:
John Doe
Welding 15 hrs.
Cleaning 4 hrs.
Bubba Smith
Fork Lift 9 hrs.
Steve Johnson
Welding 5 hrs.
This is how I figured it should be, but it's not working for me:
SELECT EmpName, SUM (Hours), JobTask
FROM tblEmpTime
Group By JobTask
Any suggestions would be greatly appreciated.
Thanks,
Parallon
Hi Experts,I am working on SSRS 2005, and I am facing a problem in counting theno of days.My database has many fields but here I am using only two fieldsThey are Placement_Date and Discharge_DateIf child is not descharged then Discharge_Date field is empty.I am writing below query to count the number of days but is is notworking it is showing the error"The conversion of a char data type to a datetime data type resultedin an out-of-range datetime value."select casewhen convert(datetime,Discharge_Date,103) = '' thendatediff(day,CONVERT(datetime,Placement_Date,103), GETDATE())elsedatediff(day,CONVERT(datetime,Placement_Date,103),CONVERT(datetime,Discharge_Date,103))end NoOfDaysfrom Placement_DetailsSo please tell me where I am wrong?Any help will be appriciated.RegardsDinesh
View 3 Replies View RelatedI'm working on Application that requires me to check and display status of reports running on report server. My application calling ListJobs() function of Job class part of Reporting Services Web Service API. When i run my application i'm getting insufficient previleges error. So i need to find out what type of permission i need to excute ListJobs().
This is very important part of my app. Please help me out.
Thanks,
Viral Patel
I debug SPS on a daily basis and I use SQL profiler to help me trace where the problem is.
Once I have established which SP is the main problem I need to debug the line of code.
What I do is Cut and Paste the SQL Profiler details and populate all the parameters,sometimes that can be 30 and more..
Now what i thought is to write an SP or Function where I pass :
SP name and Parameters that profiler genererates
and returns me Declare Statements and Set Statements with parameters filled.
EG
Profiler Returns
Customer_INSERT,20,'JO',BLOGG','5 LONDON ROAD'
I would call my new SP =PopulateSPParams and cut and paste the profiler's string
PopulateSPParams 'Customer_Insert,20,'JO',BLOGG','5 LONDON ROAD'
this will RETURN THE FOLLOWING THAT WILL IMMENSILY HELP MY DAILY PROGRAMMING.
DECLARE
@CustomerID int,
@CustomerName varchar(50),
@CustomerSurname varchar(50),
@CustomerAddress varchar(100)
SET @CustomerID =1
SET @CustomerName='JO'
SET @CustomerSurname='BLOGG'
SET @CustomerAddress='5 London Road'
Can you help in writing something that generates and populate parameters?
Hi to everyone,My problem is, that I'm not so quite sure, which way should I go.The user is inputing by second part application a long string (let'ssay 128 characters), which are separated by semiclon.Example:A20;BU;AC40;MA50;E;E;IC;GREENNow: each from this position, is already defined in any other table, asa separate record. These are the keys lets say. It means, a have someproperities for A20, BU, aso.Because this long inputed string, is a property of device (whih alsohas a lot of different properities) I could do two different ways ofstoring data:1. By writing, in SP, just encapsulate each of the position separatedby semicolon, and write into a different table with index of device,and the position in long stirng nearly in this way:Major device data tableID AnyData1 AnyData2 ... AnyData3123 MZD12 XX77 .... any comment text124 MZD13 XY55 ... any other commentString data Tablefk_deviceId position value123 1 A20123 2 BU123 3 AC40.....123 8 GREENThe device table, contains also a pointer (position), which mightchange, to "hglight" specified position.Then, I can very easly find all necessary data. The problem is, I needto move the device record data (from other table) very often into otherhistory table (by each update). That will mean, that I also need tomove all these records from 1 -8 for example to a separate historytable, holding the index for a history device dataset. This is a littleinconvinience in this, and in my opinion, it will use to much storagedata, and by programming, I need always to shift this properities intohistory table, whith indexes to a history table of other properities.2. Table will be build nearly in this way:Major device data tableID AnyData1 AnyData2 ... AnyData3 stringProperty pointer123 MZD12 XX77 .... any comment text A20;BU;AC40;MA50;E;E;IC;GREEN 3124 MZD13 XY55 ... any other comment A20;BU;AC40;MA50;E;E;IC;GREEN 2By writng into device table, there will be just a additional field forthis string, and I will have a function, which according to specifiedpointer, will get me the string part on the fly, while I need it.This will not require the other table, and will reduce the amout ofdata, not a lot ... but always.This solution, has a inconvinance, that it will be not so fast doing asearch over the part of this strings, while there will be no real indexon this.If I woould like to search all devices, by which the curent pointervalue is equal GREEN, then I need to use function for getting thevalue, and this one will be not indexed, means, by a lot amount ofdata, might be slow.I would like to know Your opinion about booth solutions.Also, if you might point me the other problems with any of thissolution, I might not have noticed.With Best RegardsMatik
View 7 Replies View Relatedhey everyone,I'm having trouble cranking out an appropriate SQL query for what I'm trying to do. I'm trying to add a ratings system into a website, I have 2 relevant tables.Files = table listing all uploaded filesRatings = table listing all ratings for filesThe two DB's are theoretically linked by the fact that the ratings table has a field for "fileID" which matches up to "fileID" in the files table. The way the page works, is that there is a querystring in the URL "filename" where "filename" is a field within the files table. So basically I'm looking for the AVG rating.ratings, where rating.fileID = files.fileID, and the appropriate files.fileID is gained from files.fileID WHERE files.filename = request.querystring["filename"]holy crap, i hope that made sense to someone here. i'm not great at explaining this. My problem is I want the AVG ratings.rating from the table ratings.... where ratings.fileID = files.fildIDWhat I have so far, that works except for the AVG part is below. This code will pull only the ratings for the appropriate file, the problem is whenever I try to add "AVG(something)" to it, the query fails.SELECT files.FID AS Expr1, files.filename, ratings.FID, ratings.ratingFROM files INNER JOIN ratings ON files.FID = ratings.FIDWHERE (files.filename = @filename) @filename value is gained from the URL querystringany ideas?
View 1 Replies View Related Hi, I'm trying to do insert some data to the database through typing the a number in a textbox. But i will get this error message whenever i tried putting a number that will be inserted to the database.
Sorry that the codes are quite messy. Please do let me know which part of the coding contains the errors.
Please help me. Thanks.
Server Error in '/WSD Project - Cam-Mart' Application.
String or binary data would be truncated.The statement has been terminated. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Data.SqlClient.SqlException: String or binary data would be truncated.The statement has been terminated.Source Error:
Line 26:
Line 27: Protected Sub add_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles add.Click
Line 28: SqlDataSource2.Insert()
Line 29: Response.Redirect("Checkout.aspx")
Line 30: Source File: C:Documents and SettingsAdministratorDesktopWSD Project - Cam-MartOrder.aspx.vb Line: 28 Stack Trace:
[SqlException (0x80131904): String or binary data would be truncated.
The statement has been terminated.]
System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +177
System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +68
System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +199
System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +2300
System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) +147
System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) +1021
System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) +314
System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) +413
System.Data.SqlClient.SqlCommand.ExecuteNonQuery() +115
System.Web.UI.WebControls.SqlDataSourceView.ExecuteDbCommand(DbCommand command, DataSourceOperation operation) +392
System.Web.UI.WebControls.SqlDataSourceView.ExecuteInsert(IDictionary values) +405
System.Web.UI.WebControls.SqlDataSource.Insert() +13
Order.add_Click(Object sender, EventArgs e) in C:Documents and SettingsAdministratorDesktopWSD Project - Cam-MartOrder.aspx.vb:28
System.Web.UI.WebControls.Button.OnClick(EventArgs e) +75
System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +97
System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +7
System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +11
System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +33
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +4921
Version Information: Microsoft .NET Framework Version:2.0.50727.42; ASP.NET Version:2.0.50727.210
The codes that i have is in Order.aspx:
<%@ Page Language="VB" MasterPageFile="~/MainMasterPage.master" AutoEventWireup="false" CodeFile="Order.aspx.vb" Inherits="Order" title="Cam-Mart - Your Cart" %>
<asp:Content ID="Content1" ContentPlaceHolderID="ContentPlaceHolder1" Runat="Server">
<asp:TextBox ID="ProductID" runat="server" Visible="False"></asp:TextBox>
<asp:TextBox ID="ProductPrice" runat="server" Visible="False"></asp:TextBox><br />
<span style="font-family: Verdana; color: lightsteelblue;">Product Name: </span>
<asp:Label ID="prodName" runat="server" Text="Label" ForeColor="Black"></asp:Label><br />
<span style="font-family: Verdana; color: lightsteelblue;">Product Price: </span><asp:Label ID="price"
runat="server" Text="Label" ForeColor="Black"></asp:Label><br />
<span style="font-family: Verdana"><span style="color: lightsteelblue">Quantity:</span>
</span><asp:TextBox ID="tbQuantity" runat="server" AutoPostBack="True"></asp:TextBox>
<asp:Label ID="lblqty" runat="server" Text="Label"></asp:Label><br />
<asp:TextBox ID="ProductName" runat="server" Visible="False"></asp:TextBox><br />
<br />
<asp:Label ID="Label1" runat="server" Text="Label" Visible="False"></asp:Label><br />
<span style="color: lightsteelblue; font-family: Verdana">Your Order Date is: </span>
<asp:Label ID="lblOrderDate" runat="server"></asp:Label><br />
<span style="color: lightsteelblue; font-family: Verdana">Your Delivery Date will be:</span>
<asp:Label ID="lblDeliveryDate" runat="server"></asp:Label><br />
<asp:TextBox ID="dtorderdate" runat="server" Visible="False"></asp:TextBox>
<br />
<asp:TextBox ID="dtdeliverydate" runat="server" Visible="False"></asp:TextBox><br />
<br />
<asp:Button ID="add" runat="server" Text="Add to Cart" /><br />
<asp:Button ID="Back" runat="server" Text="Back" /><br />
<asp:Button ID="checkout" runat="server" Text="Checkout" /><br />
<asp:FormView ID="FormView1" runat="server" DataKeyNames="CustID" DataSourceID="SqlDataSource1">
<EditItemTemplate>
CustID:
<asp:Label ID="CustIDLabel1" runat="server" Text='<%# Eval("CustID") %>'></asp:Label><br />
<asp:LinkButton ID="UpdateButton" runat="server" CausesValidation="True" CommandName="Update"
Text="Update">
</asp:LinkButton>
<asp:LinkButton ID="UpdateCancelButton" runat="server" CausesValidation="False" CommandName="Cancel"
Text="Cancel">
</asp:LinkButton>
</EditItemTemplate>
<InsertItemTemplate>
<asp:LinkButton ID="InsertButton" runat="server" CausesValidation="True" CommandName="Insert"
Text="Insert">
</asp:LinkButton>
<asp:LinkButton ID="InsertCancelButton" runat="server" CausesValidation="False" CommandName="Cancel"
Text="Cancel">
</asp:LinkButton>
</InsertItemTemplate>
<ItemTemplate>
CustID:
<asp:Label ID="CustIDLabel" runat="server" Text='<%# Eval("CustID") %>'></asp:Label><br />
</ItemTemplate>
</asp:FormView>
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:List Product %>"
SelectCommand="SELECT [CustID] FROM [CustInfo] WHERE ([CustName] = @CustName)">
<SelectParameters>
<asp:ControlParameter ControlID="Label1" Name="CustName" PropertyName="Text" Type="String" />
</SelectParameters>
</asp:SqlDataSource>
<asp:SqlDataSource ID="SqlDataSource2" runat="server" ConflictDetection="CompareAllValues"
ConnectionString="<%$ ConnectionStrings:List Product %>" DeleteCommand="DELETE FROM [OrderDetail] WHERE [OrderID] = @original_OrderID AND [CustID] = @original_CustID AND [CustName] = @original_CustName AND [OrderDate] = @original_OrderDate AND [TotalValue] = @original_TotalValue AND [Quantity] = @original_Quantity AND [DeliveryDate] = @original_DeliveryDate AND [ProductID] = @original_ProductID AND [ProductName] = @original_ProductName"
InsertCommand="INSERT INTO [OrderDetail] ([CustID], [CustName], [OrderDate], [TotalValue], [Quantity], [DeliveryDate], [ProductID], [ProductName]) VALUES (@CustID, @CustName, @OrderDate, @TotalValue, @Quantity, @DeliveryDate, @ProductID, @ProductName)"
OldValuesParameterFormatString="original_{0}" SelectCommand="SELECT * FROM [OrderDetail] WHERE ([CustID] = @CustID)"
UpdateCommand="UPDATE [OrderDetail] SET [CustID] = @CustID, [CustName] = @CustName, [OrderDate] = @OrderDate, [TotalValue] = @TotalValue, [Quantity] = @Quantity, [DeliveryDate] = @DeliveryDate, [ProductID] = @ProductID, [ProductName] = @ProductName WHERE [OrderID] = @original_OrderID AND [CustID] = @original_CustID AND [CustName] = @original_CustName AND [OrderDate] = @original_OrderDate AND [TotalValue] = @original_TotalValue AND [Quantity] = @original_Quantity AND [DeliveryDate] = @original_DeliveryDate AND [ProductID] = @original_ProductID AND [ProductName] = @original_ProductName">
<DeleteParameters>
<asp:Parameter Name="original_OrderID" Type="Int32" />
<asp:Parameter Name="original_CustID" Type="Int32" />
<asp:Parameter Name="original_CustName" Type="String" />
<asp:Parameter Name="original_OrderDate" Type="DateTime" />
<asp:Parameter Name="original_TotalValue" Type="Decimal" />
<asp:Parameter Name="original_Quantity" Type="Int32" />
<asp:Parameter Name="original_DeliveryDate" Type="DateTime" />
<asp:Parameter Name="original_ProductID" Type="Int32" />
<asp:Parameter Name="original_ProductName" Type="String" />
</DeleteParameters>
<UpdateParameters>
<asp:Parameter Name="CustID" Type="Int32" />
<asp:Parameter Name="CustName" Type="String" />
<asp:Parameter Name="OrderDate" Type="DateTime" />
<asp:Parameter Name="TotalValue" Type="Decimal" />
<asp:Parameter Name="Quantity" Type="Int32" />
<asp:Parameter Name="DeliveryDate" Type="DateTime" />
<asp:Parameter Name="ProductID" Type="Int32" />
<asp:Parameter Name="ProductName" Type="String" />
<asp:Parameter Name="original_OrderID" Type="Int32" />
<asp:Parameter Name="original_CustID" Type="Int32" />
<asp:Parameter Name="original_CustName" Type="String" />
<asp:Parameter Name="original_OrderDate" Type="DateTime" />
<asp:Parameter Name="original_TotalValue" Type="Decimal" />
<asp:Parameter Name="original_Quantity" Type="Int32" />
<asp:Parameter Name="original_DeliveryDate" Type="DateTime" />
<asp:Parameter Name="original_ProductID" Type="Int32" />
<asp:Parameter Name="original_ProductName" Type="String" />
</UpdateParameters>
<SelectParameters>
<asp:ControlParameter ControlID="FormView1" Name="CustID" PropertyName="SelectedValue"
Type="Int32" />
</SelectParameters>
<InsertParameters>
<asp:ControlParameter Name="CustID" Type="Int32" ControlID="FormView1"/>
<asp:ControlParameter Name="OrderDate" Type="DateTime" ControlID="dtorderdate"/>
<asp:ControlParameter Name="TotalValue" Type="Decimal" ControlID="price"/>
<asp:ControlParameter Name="Quantity" Type="Int32" ControlID="lblqty"/>
<asp:ControlParameter Name="DeliveryDate" Type="DateTime" ControlID="dtdeliverydate"/>
<asp:ControlParameter Name="ProductID" Type="Int32" ControlID="ProductID"/>
<asp:ControlParameter Name="ProductName" Type="String" ControlID="prodName"/>
<asp:ControlParameter Name="CustName" Type="String" ControlID="Label1"/>
</InsertParameters>
</asp:SqlDataSource>
</asp:Content>
The text in Order.aspx.vb is :
Partial Class Order
Inherits System.Web.UI.Page
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
'Dim sUsername As String = " "
Session("Username") = Request.ServerVariables("Auth_user")
Label1.Text = Session("Username")
ProductID.Text = Request("ProductID")
ProductPrice.Text = Request("ProductPrice")
ProductName.Text = Request("ProductName")
lblqty.Text = tbQuantity.Text
prodName.Text = ProductName.Text
price.Text = ProductPrice.Text
dtorderdate.Text = DateTime.Today
dtdeliverydate.Text = DateAdd("d", 7, Date.Today)
lblOrderDate.Text = dtorderdate.Text
lblDeliveryDate.Text = dtdeliverydate.Text
'Dim dtorderdate, dtdeliverydate As DateTime
'lblOrderDate.Text = "Order date: " & dtorderdate.ToString
'lblDeliveryDate.Text = "Delivery date: " & dtdeliverydate.ToString
End Sub
Protected Sub add_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles add.Click
SqlDataSource2.Insert()
Response.Redirect("Checkout.aspx")
End Sub
Protected Sub Back_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Back.Click
Response.Redirect("Catalogs.aspx")
End Sub
Protected Sub checkout_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles checkout.Click
Response.Redirect("Checkout.aspx")
End Sub
End Class
Hi!
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
does anyone know a function to count the number of times a substring appears in a string?
example NumberOfTimes("i","mississippi") would return 4
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?
Thanks
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.
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
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
thanks
SlayerS_`BoxeR` + [ReD]NaDa
select distinct(f.agency+y.year+s.type) 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 RelatedIs there a way to convert special characters such ö to o?
Cheers
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.
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.
TIA
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.
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
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 RelatedHope 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: MFD-2fdr4.zip
CREATE FUNCTION Parse(String, '-' , '.')
.....
....
.....
END
and it shoud return 2fdr4
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)
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
BEGIN
'''''''''''''''''''...........................
DECLARE @vNextProjectID varchar(10)
RETURN @vNextProjectID
END
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
cmd.ExecuteScalar()
vProjectID = cmd.Parameters("@vNextProjectID").Value
txtProjectID.Text = vProjectID
cmd.Dispose()
End Sub
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 RelatedI 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")?
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 RelatedHi,Does anyone have a function which replaces accent chars from a stringwith the non-accented equivalent? For example 'hôpital' should return'hopital'.Thank you in advance.
View 3 Replies View Related
Hi all
Which function should I use to remove alphabet in a string?
For example, 60a , 50b, 34s, 34k. I want to remove the suffix alphabet. I tried to use filter but it return an array. i want the return value to be string or int to display.
Thanks
Bryan
Im working on a db library and I have everything working, what Im wanting to do is though is add a method that can check a connection string to make sure it is actually working. Right now, I have it doing a simple select * query to a particular table and returning true or false if an exception is caused. But I want to make the library as generic as possible, so is there another way to test teh connection string and tell if its working or not?Thanks,Cedric
View 1 Replies View RelatedHi,
As part of a data search project I need to be able to strip all non numeric characters from a text field. The field contains various forms of phone number in various formats. In order to search on it I am going to remove all non numeric characters from the input criteria and from the data being searched.
In order to do this I decided on using a SQL Server custom function: Pass in field. Loop through all chars, test against asci values for number range. return only numernic data concatenated into a string.
Are there any other more efficient ways of going about this?
I have created a function with:
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[fn_concat_boxes](@item varchar, @week int)
RETURNS VARCHAR(100)
AS
BEGIN
DECLARE @Output varchar(100)
SELECT @Output = COALESCE(@Output + '/', '') +
CAST(quantity AS varchar(5))
FROM flexing_stock_transactions
WHERE item = @item AND week = @week
GROUP BY quantity
ORDER BY quantity
RETURN @Output
END
how can I pass the variable @item correctly for the string comparison
WHERE item = @item AND week = @week
to work correctly please?
WHERE item = '@item' AND week = @week
won't work and
WHERE item = @item AND week = @week
won't work.