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.
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
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
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
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?
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?
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.
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.
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
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.
Hope someone can help... I need a function to parse a string using a beginning character parameter and an ending character parameter and extract what is between them. For example.....
Here is the sample string: MFD-2fdr4.zip
CREATE FUNCTION Parse(String, '-' , '.') ..... .... ..... END
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"
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
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
Hi,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.
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.
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
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?
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
Hi Let€™s say I have employees table that contains id column for the supervisor of the employee. I need to create a function that gets coma separated string value of the supervisors€™ ids, And return the ids of employees that the ENTIRE listed supervisors are there supervisor. (some thing like €œSelect id from employees where supervisor=val_1 and supervisor=val_2 and €¦ and supervisor=val_N) Is there a way to create this function without using sp_exec?
I€™ve created a function that splits the coma separated value to INT table. (For use in a function that do something like: €œSelect id from employees where supervisor in (select val from dbo.SplitToInt(coma_separated_value)) )
Hi All!!! I was tasked to come up with a search function and the content of the database given to me is in Chinese Characters. This would be my first time dealing with Chinese characters in the database and I need help with the following problem: The company wants to conduct the search in such a way that, instead of having the system read the entire sentence/phrase which the user keyed in as a SINGLE string, they want the Chinese Characters to be accessed individually, so that as long as any information in the database contains any one of the characters which the user have entered, they will be retrieved and returned. So how do I go about doing this? Does it have anything to do with Unicode? By the way, everything abt the search tool is working fine, I am just left with this dilemma of having the system recognise the entire sentence as ONE STRING, instead of conducting a search word by word or character by character. Anyway, the following is the SQL statement of my SQL Data Source which is bound to a Gridview displaying the returned results after a search is done...1 SELECT Name, Trans, Address1, Address1T, Address2, Address2T, City, CityT, CRPLID 2 FROM CRPL 3 WHERE (Trans LIKE '%' + @Trans + '%') OR 4 (Name LIKE '%' + @Name + '%') OR 5 (Address1 LIKE '%' + @Address1 + '%') OR 6 (Address1T LIKE '%' + @Address1T + '%') OR 7 (Address2 LIKE '%' + @Address2 + '%') OR 8 (Address2T LIKE '%' + @Address2T + '%') OR 9 (City LIKE '%' + @City + '%') OR 10 (CityT LIKE '%' + @CityT + '%')
What i need is to create a function that compares 2 strings variables and if those 2 variables doesn't have at least 3 different characters then return failure , else return success.
Consider the following: I have a table, say ORDERS, with these entries -
CustID ProductID 1Â Â Â Â Â Â CAN 2Â Â Â Â Â Â 2 3Â Â Â Â Â Â 1,2 4Â Â Â Â Â Â 4 5Â Â Â Â Â Â 1,2,3,4,5,CAN 6Â Â Â Â Â Â 10 7Â Â Â Â Â Â CAN 8Â Â Â Â Â Â Â 1,CAN
I'd like to write a script to return only those rows WHERE ProductID = CAN along with other values in the same column. In this example, I'd like to return rows 5 & 8. How can I write this in T-SQL? So, say, check if ProductID has a comma ',' value plus the 'CAN' string. If yes, then return that row. If I use the LIKE operator, it'll return rows 1,5,7, and 8.
