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


ADVERTISEMENT

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

Script Component Task: Function That Returns Last Word In A String

Sep 7, 2006

I have a field called CustomerName Varchar 100 and I wish to write a function that can do the following in a script component task

create a function called CleanString (ByVal CustomerName as String) As String

CleanString Returns  the last word of a Customer name if the CustomerName field contains more than one word or if the CustomerName field does not contain Corp or Ltd

ie parse 'Mr John Tools'  and the function returns 'Tools'

ie parse 'TechnicalBooks' and the function returns 'TechnicalBooks'

ie parse 'Microsoft Corp' return 'Microsoft Corp'

ie parse 'Digidesign Ltd' return 'Digidesign Ltd'

Any idea of a regular expression or existing piece of existing code I can have

thanks in advance

dave

 

View 3 Replies View Related

Function That Returns Table

Sep 5, 2006

Hello,



Is there any way to write a function where I can write some code and at the end of the code return a entire table as parameter??

View 4 Replies View Related

Function That Returns Highest Of Two Columns?

Jul 20, 2005

Is there a function that compares two columns in a row and will returnthe highest of the two values? Something like:Acct Total_Dollars Collected Total_Dollars_Due11233 900.00 1000.00Declare @Value as moneyset @Value=GetHighest(Total_Dollars_Collected,TotalDol lars_Due)Print @ValueThis function will return 1000.00 or the Total_dollars_Due??Is there such a creature???

View 2 Replies View Related

Analytic Function Returns Wrong Values With AVG

Feb 4, 2014

I have following select-statement:

select [date], [close], AVG([close]) over (order by [date] rows between 2 preceding and current row ) as [ma]
from dax2

My Problem is that the first 2 rows in column [ma] are not correct. They Show a value since it is not a 3 days average. In the first row in column [ma]is the same value as in [Close]. In the second row in column [ma] is the average value of the first and second value of column [Close].

How can i achieve that this "erroneous" values are not inserted or rather are shown as null.

View 2 Replies View Related

Augent Pls Help::::Is_Member() Function Returns 0 Instead Of 1 In Sql2005

Aug 10, 2007

Hello All,

I have a situation where after creating roles and addding users to the roles then when i call Is_Member('MyRole') on my development box it tells me the user is a member of the Role,
But when i detach the database and deploy on a production server and call Is_Member('MyRole') it tells me that the user is not a member of the Role....

I went on to call the system stored procedures, sp_helprolemember to actualy determine if the user is in the 'MyRole' role and the procedure returned a list which i confirmed that the user actually exists in that role..

So am pretty confused when i call Is_Member('MyRole') and it gives me 0 meaning the user is not in the role....

Plss i need help on this thanks a million


KNOW THY SELF

View 8 Replies View Related

Find A Function That Returns The Column Width

Mar 3, 2008


Dear all hi,

I would like to create a function that will return to me the width of the column of the table without giving as a parameter the table name. I need it, as I want to exceed my data in this column to the column length. I want to fill the column data with trailing blanks until I reach the column width. The function len returns the length of the data in the column.

I know that If I have the table name I can find the column width through the system tables. I don€™t want this.

Sincerely,
Hellen

View 10 Replies View Related

SQL 2012 :: CheckSum Agg Function Returns 0 For Even Number Of Repeated Values?

Aug 14, 2014

From what I've seen, the CheckSum_Agg function appears to returns 0 for even number of repeated values. If so, then what is the practical use of this function for implementing an aggregate checksum across a set of values?

For example, the following work as expected; it returns a non-zero checksum across (1) value or across (2) unequal values.

declare @t table ( ID int );
insert into @t ( ID ) values (-7077);
select checksum_agg( ID ) from @t;
-----------
-7077
declare @t table ( ID int );
insert into @t ( ID ) values (-7077), (-8112);
select checksum_agg( ID ) from @t;
-----------
1035

However, the function appears to returns 0 for an even number of repeated values.

declare @t table ( ID int );
insert into @t ( ID ) values (-7077), (-7077);
select checksum_agg( ID ) from @t;
-----------
0

It's not specific to -7077, for example:

declare @t table ( ID int );
insert into @t ( ID ) values (-997777), (-997777);
select checksum_agg( ID ) from @t;
-----------
0

What's curious is that (3) repeated equal values will return a checksum > 0.

declare @t table ( ID int );
insert into @t ( ID ) values (-997777), (-997777), (-997777);
select checksum_agg( ID ) from @t;
-----------
-997777

But a set of (4) repeated equal values will return 0 again.

declare @t table ( ID int );
insert into @t ( ID ) values (-997777), (-997777), (-997777), (-997777);
select checksum_agg( ID ) from @t;
-----------
0

Finally, a set of (2) uneuqal values repeated twice will return 0 again.

declare @t table ( ID int );
insert into @t ( ID ) values (-997777), (8112), (-997777), (8112);
select checksum_agg( ID ) from @t;
-----------
0

View 0 Replies View Related

T-SQL (SS2K8) :: User Defined Function That Returns Start And End Date

Oct 15, 2014

Any UDF that accepts a Month and Year and returns Start Date and End Date.

For example @Month = 01 and @Year = 2014 would return a StartDate of 2014-01-01 and an EndDate of 2014-01-31.

View 8 Replies View Related

Why Search On Empty String Returns Results With A Space?

Aug 1, 2007

In sql server 2000 - our QA pointed out that his testing for empty strings returned 200 + rows but that when he clicked in the field there were obviously a space there. This issue came up because of the script I created to replace and earlier one that queried on empty strings instead of datalength and the earlier script always reported that it had updated x number of rows regardless of how many times it was run on the same database.

QA query based on the earlier script:
Select * from StringTable
WHERE (LongString = '' OR LongString IS NULL)

My script:
The fields are nvarchars in the newer database but older version of the database had varchars. I had created a script to replace empty strings as follows:

-- if LongString column is varchar - run varchar update else nvarchar update
If exists (Select * from sysobjects o
inner join syscolumns c on c.id = o.id
where c.name = 'LongString' and o.name = 'StringTable' and c.xtype = 167) begin

-- update varchar LongString
UPDATE StringTable
SET LongString = char(32)
-- Select * from StringTable
WHERE ((DATALENGTH(LongString ) < 1) OR LongString IS NULL)

END
Else Begin

-- update nvarchar LongString
UPDATE StringTable
SET LongString = char(32)
-- Select * from StringTable
WHERE ((DATALENGTH(LongString ) < 2) OR LongString IS NULL)

END

If exists (Select * from sysobjects o
inner join syscolumns c on c.id = o.id
where c.name = 'ShortString' and o.name = 'StringTable' and c.xtype = 167) begin

UPDATE StringTable
SET ShortString= char(32)
-- Select * from StringTable
WHERE ((DATALENGTH(ShortString) < 1) OR ShortString IS NULL)

END
Else Begin

-- update nvarchar ShortString
UPDATE StringTable
SET ShortString= char(32)
-- Select * from StringTable
WHERE ((DATALENGTH(ShortString) < 2) OR ShortString IS NULL)

END

My method for checking for datalength appears to work correctly why doesn't the QA script? I thought it might have to do with the nvarchar used in the table but I changed the column to a varchar and still has the same issue.

Thanks

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

Transact SQL :: Table Valued Function Returns Nothing When Single Column Selected

Apr 21, 2015

We run std 2008 r2.  I haven't looked at my friend's function closely yet bur he showed me that when he selects from the function with one column and the same where clause he uses on same func with select *, he gets no data under the column he requested. 

But when he selects * he gets a single row.

I took a peek and see a bunch of left joins followed by a bunch of outer applies in his func. I suppose (thinking out load) if anything random like the order of rows returned or sql decisions on how query runs can affect his function, that might explain it.  

View 8 Replies View Related

Transact SQL :: Function To Find Last Date Of Month One Year Ago - RETURNS ERROR

Apr 28, 2015

I've written sql code which takes a date and finds the Last Day of the Month one year ago. For example,  it takes the date '2015-04-17' and returns the date '2014-04-30'. The code works fine in a query. Now I'm trying to turn this into a function. However, when I try to create the function I get the error:

Operand type clash: date is incompatible with int

Why is this error being returned?

Here is my function:

CREATE FUNCTION dbo.zEOM_LY_D(@Input Date)
       RETURNS date
AS
BEGIN;
  DECLARE @Result date;
  SET @Result =  convert(DATE, DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,dateadd(m, -11, @Input)+1),0)),101)
    RETURN @Result;
END;

View 11 Replies View Related

SQL Server User Defined Function Treating String As Integer

Oct 3, 2006

I have a User Defined Function in my SQL Server 2000 database which takes a string and adds an integer quantity to it.  The function basically takes the string (string because of first character and spaces) and adds an integer to it creating a new number (starting number and ending number concept).If I pass in a string that has a letter for the first character, it works fine.  However, if the first character is an integer it trims out all 0s and whitespace and ruins the necessary formatting.  Note, the formatting is always the same - x xxx xxxx xx.   Any help or ideas would be appreciated.Example:D 499 8900 01     plus a Quantity of 10 returns D 499 89000 11  which is perfect.However,0 076 0000 03     plus a Quantity of 1 returns  764  764     (it should be 0 076 0000 04)------------------------------------CREATE FUNCTION  [dbo].[NEW_End_NR2](  @OldStr as char(20), @Quantity int )RETURNS @NewNR_Tbl TABLE (New_EndNr char(20) primary key)AS  BEGIN DECLARE @NewStr  char(20)  DECLARE @addNr  integerBEGIN  Set @addNr = @Quantity set @NewStr = (select REPLACE(@OldStr,left(@OldStr, 1),'')) set @NewStr = (select REPLACE(@NewStr,' ', ''))  + @addNr set @NewStr = (SELECT left(@OldStr, 1) +    SPACE(1) + left(@NewStr,3) +    SPACE(1) + left(REPLACE(@NewStr, left(@NewStr,3), ''), 4) +   SPACE(1) + left(REPLACE(@NewStr, left(@NewStr,7), ''), 3)) BEGIN  INSERT INTO @NewNR_Tbl (New_EndNr) VALUES(@NewStr) END END    RETURNEND

View 3 Replies View Related

Update Function: Why SQL Server Update An Empty String With 0?

May 13, 2008

I'm new to this forum.
This 'problem' has occured many times, but I've always found a way around it.
I have pages with datagrids, in which a user can edit a certain fields and then update the tables with new data. Lets say when a user edit a Name field and a money field. If he/she left those two fields blank, the table is automatically updated with a <null> (for the name field) and a 0 (for the money field.) Both these columns were set up to allow Null values.
Anyone has an idea why they were updated that way? And is there like a standard on how the data types are updated if a field is left blank?
Thank you very much.

View 23 Replies View Related

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

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







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