April 7, 2011 at 8:44 am
Hi
I have been trawling the web to find out if there is a way to create a simple string manipulation udf that returns a variable length string after the manipulation has been done. I would appreciate it if anybody can tell me if this is at all possible and if so how. I need to use this function in a view that must have the correct string lengths in the view columns. I dont want to use CAST on top of this function for every column in the view.
Below is the pseudo code:
CREATE FUNCTION [dbo].[VariableString](@Input varchar(1000),@Length int)
RETURNS varchar(@Length) -- Here I want the length of the return string to be whatever the value of @Length is
AS
BEGIN
DECLARE @Return varchar(1000)
blah blah fishpaste fishpaste
SET @Return=SUBSTRING(@Input, 1, @Length)
RETURN @Return
END
April 7, 2011 at 8:55 am
What you've got should work - just change varchar(@Length) to varchar(1000). Not sure why you need a UDF, though - the built-in function LEFT should do exactly what you need.
John
April 7, 2011 at 9:01 am
This way wont work because then varchar(1000) will be returned every time. I want to call the function and have the correct string length returned based on the length parameter
April 7, 2011 at 9:13 am
The correct string length will be returned. Try the code in the attached file.
John
April 7, 2011 at 9:28 am
From books online
User-defined functions cannot be used to perform actions that modify the database state.
The datatype returned by a function is part of the database state, so you can't write a function that dynamically updates the datatype even if it's just a change to the allowed length.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
April 7, 2011 at 10:04 am
It sounds like what you're asking for is to return a string of a specified length given an input string of varying length.
When your input string is LONGER than your @Length parameter, what you have already in code will work. It will truncate the string to your specified length.
If you specify a @Length that is LONGER than your @Input, you want to PAD it out, probably with spaces. So, try the following:
SET @Return=SUBSTRING(@Input + SPACE(@Length), 1, @Length)
Beware, though, as many of T-SQL's string functions will trim trailing spaces, so you'll still get, for example, the length of the actual string without spaces from the LEN() function, even though the padding spaces are there.
Edit: I forgot to note that, as others have said, you can't define the size of the return variable on the fly as you've tried to do:
RETURNS varchar(@Length) -- Here I want the length of the return string to be whatever the value of @Length is
Instead, simply specify the return type as varchar(1000). As I said earlier, however, beware of how TSQL treats the return value when used in various contexts.
Rob Schripsema
Propack, Inc.
April 7, 2011 at 2:42 pm
trevor.howe (4/7/2011)
HiI have been trawling the web to find out if there is a way to create a simple string manipulation udf that returns a variable length string after the manipulation has been done. I would appreciate it if anybody can tell me if this is at all possible and if so how. I need to use this function in a view that must have the correct string lengths in the view columns. I dont want to use CAST on top of this function for every column in the view.
Below is the pseudo code:
CREATE FUNCTION [dbo].[VariableString](@Input varchar(1000),@Length int)
RETURNS varchar(@Length) -- Here I want the length of the return string to be whatever the value of @Length is
AS
BEGIN
DECLARE @Return varchar(1000)
blah blah fishpaste fishpaste
SET @Return=SUBSTRING(@Input, 1, @Length)
RETURN @Return
END
In short...No, it cannot be done. T-SQL is declarative, emphasis on declare. You cannot change an interface's type on the fly.
What are you trying to avoid or solve (exmaple please)? Maybe we can work the problem from a different angle and come up with a decent solution.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
April 7, 2011 at 11:15 pm
Thanks for all the replies. Looks like what I am trying to do is not do-able in SQL Server.
As requested by Mr or Mrs. 500, here is an overview of what I am trying to achieve:
I am working on loading source data for a BI initiative. We are busy with data scrubbing. Each files data gets cleaned up and landed into another table. The scurbbing is done via a view which is the source in an SSIS package. The destination is a scrub table with predefined column lengths. The string scrubbing function is one of many (there are others for dates and integers). The string scrub function converts to the required case, trims of trailing and leading blanks and sets the field length to the required length required by the destination table in the SSIS package. So thats why I need the columns in the view to be in the correct length for the string data.
Currently my view looks something like this (The function has 3 parameters - String to scrub, desired return length, and 1/2/3 for converstion to UPPER/LOWER/Title Case):
SELECT
CAST(ScrubString(Field1,3,1) AS VARCHAR(3)) AS NewFieldName
and other fields with different functions for different scrub types...
FROM SourceTable
All fields in SourceTable are currently coming in as varchar(255)
I am trying to get around using the CAST statement to get the destination field metadata to the correct length. If I dont CAST it, they all come through as VARCHAR(255). SSIS gives warnings (not errors) for the fields because the field length is longer than the field it is trying to insert to.
Hope that explains it. I know this is not a serious error but I dont like having to do the CAST in the view statement when this is already been done in the UDF.
Thanks
April 7, 2011 at 11:35 pm
bozzybox (4/7/2011)
Thanks for all the replies. Looks like what I am trying to do is not do-able in SQL Server.As requested by Mr or Mrs. 500, here is an overview of what I am trying to achieve:
I am working on loading source data for a BI initiative. We are busy with data scrubbing. Each files data gets cleaned up and landed into another table. The scurbbing is done via a view which is the source in an SSIS package. The destination is a scrub table with predefined column lengths. The string scrubbing function is one of many (there are others for dates and integers). The string scrub function converts to the required case, trims of trailing and leading blanks and sets the field length to the required length required by the destination table in the SSIS package. So thats why I need the columns in the view to be in the correct length for the string data.
Currently my view looks something like this (The function has 3 parameters - String to scrub, desired return length, and 1/2/3 for converstion to UPPER/LOWER/Title Case):
SELECT
CAST(ScrubString(Field1,3,1) AS VARCHAR(3)) AS NewFieldName
and other fields with different functions for different scrub types...
FROM SourceTable
All fields in SourceTable are currently coming in as varchar(255)
I am trying to get around using the CAST statement to get the destination field metadata to the correct length. If I dont CAST it, they all come through as VARCHAR(255). SSIS gives warnings (not errors) for the fields because the field length is longer than the field it is trying to insert to.
Hope that explains it. I know this is not a serious error but I dont like having to do the CAST in the view statement when this is already been done in the UDF.
Thanks
opc.three here (Mr or Mrs 500 is just my rating name on the site based on my point total)
<opinion>T-SQL is the lesser choice to do what you're describing when SSIS is an option. You ought to look at moving all your string manip into SSIS...it will be cleaner and will free your SQL Server up to do more DB-centric tasks like retrieving and storing data.</opinion>
Once you have a cleaned up string in SSIS (regardless of whether you decide to stay in T-SQL views to deliver that or move the initial scrub into SSIS) you can use the SUBSTRING function within the expressions offered in the Derived Column component to change your string lengths to the destination column length before pushing it to your data flow destination.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy