STRING FUNCTION

May 14, 2001

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

View 1 Replies


ADVERTISEMENT

A String Function

Aug 25, 1999

does anyone know a function to count the number of times a substring appears in a string?
example NumberOfTimes("i","mississippi") would return 4

View 3 Replies View Related

Right And String Function

Jun 21, 2001

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

View 4 Replies View Related

SQL String Function Help

Mar 13, 2006

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.

View 5 Replies View Related

String Function

Jun 23, 2006

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

View 5 Replies View Related

SUM Function For String????

Aug 16, 2007

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

View 12 Replies View Related

String Function

Dec 19, 2006

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 Related

String Function

Jan 23, 2008

Is there a way to convert special characters such ö to o?

Cheers

View 3 Replies View Related

String Function Needed

Sep 26, 2006

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?  

View 1 Replies View Related

Is There AT-SQL Function Like VBScript STRING() ?

Aug 11, 2000

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.

View 1 Replies View Related

String Function Questions

Jan 10, 2001

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

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

SQL Search String Function

Jun 2, 2008

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

View 4 Replies View Related

LEFT And String Function

Nov 3, 2006

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 Related

Function To Parse A String

Mar 13, 2008

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


and it shoud return 2fdr4

View 8 Replies View Related

FINDSTRING String Function

Sep 21, 2007

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)

View 4 Replies View Related

MS SQL Function Return String - What Am I Doing Wrong?

Jun 6, 2005

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

View 4 Replies View Related

Sql Server Function Returns Xml String

Oct 21, 2005

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 Related

Write String Default Value Function

Jun 3, 2008

I 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")?

View 5 Replies View Related

ISNULL Function And String Concatenation

Jul 20, 2005

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 Related

Function To Remove Accent In String

Jul 20, 2005

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.

View 3 Replies View Related

Which Function Should I Use To Remove Alphabet In A String?

Feb 13, 2008



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

View 4 Replies View Related

Is There A Sql Database Function To Check Connection String?

Nov 1, 2005

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 Related

Stripping Non-Numeric Characters From A String Function

Jul 9, 2002

Hi,

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?

View 4 Replies View Related

Passing Variable To String Compare In Function

Dec 8, 2007

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.

View 2 Replies View Related

Coma Separated String Value As Function Parameter

Aug 30, 2007

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

Thanks ,
Z

View 8 Replies View Related

SSIS Convertion Function From Integer To String

Dec 1, 2005

Hello all

View 22 Replies View Related

Any Function That Returns The Position Of A String In A Column?

Dec 12, 2007

Hello!
Is there a function that gets the name of a column and a string as arguments and returns the position of this string in the column given?

Thank you in advance.

View 1 Replies View Related

URGENT!!! Search Tool Function: Splitting A String

Jan 14, 2008

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 + '%')

 
Thanks for all your help in advance!!!

View 6 Replies View Related

SQL Server 2014 :: Function To Convert Datetime To String

Jan 26, 2014

Writing a SQL Function as below

the input parameter for function should be datetime of sql datetimeformat

and out put should be a string = yyyymmdd1 or yyyymmdd2

The last character 1 or 2 based on below condition

if time is between 6AM and 5.59PM then 1
if 6PM to 5.59AM then 2

View 6 Replies View Related

SQL Server 2012 :: String Variables Comparison Function

Aug 10, 2015

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.

View 9 Replies View Related

Limit Length Of String Returned From Format Function?

Dec 23, 2013

When I use for instance:

SELECT
FORMAT(BegDate,"dd.mm.yyyy")
FROM TABLE1
result is - |BegDate|22.12.2013.......................|

Is there any way to limit length of string returned from FORMAT function .

Database is on ACCESS.

View 1 Replies View Related

Transact SQL :: String Function To Extract Matching Rows

Oct 6, 2015

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.

View 12 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved