User Function

Question

User Function

BAGs

Right there with Babe

Points: 793
More actions
February 26, 2008 at 1:32 pm

#74634

Good Afternoon
I have a unique field in a table that i created from a series of fields from a transaction file. THe field is similar to this:
Store Number & Date & Register Num & Transaction Number and may actually look like:
str001dt020208reg005trn0001
i want to create a function to strip out the individual pieces i mentioned above. If i were to do this using VB i would pass the field into a function and using left, right, mid etc. i could get at what i wanted.
Using SQL2005 do i employ a similar concept. There really is no select statement involved. What i hope to be able to do is something like:
SELECT MyStoreFunc([Field]) from MyTable
and return
str001
str002
str003 etc...
HELP
thank you in advance

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 1

Look up creating a "scalar function". That's a function returning a single value as opposed to returning an entire table.

create function myfunc(@myparams int, ...)

RETURNS @bob-2 int

as

BEGIN

.....

RETURN

END

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

BAGs Right there with Babe Points: 793 More actions · Answer 2

thank you Matt

i will do that and let you know how i make out.

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 3

DECLARE@Sample TABLE (Data VARCHAR(100))

INSERT@Sample

SELECT'str001dt020208reg005trn0001'

SELECTs.Data,

t.*

FROM@Sample AS s

CROSS APPLY(

SELECTSUBSTRING(Data, 1, 6) AS Store,

SUBSTRING(Data, 7, 8) AS Date,

SUBSTRING(Data, 15, 6) AS RegisterNum,

SUBSTRING(Data, 21, 7) AS TransactionNum

) AS t

N 56°04'39.16"
E 12°55'05.25"

Jeff Moden SSC Guru Points: 1004361 More actions · Answer 4

Ummm... why is Cross-Apply necessary here? Seems to be a bit of overkill when the simple substrings would probably do just fine...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 5

Just in case there are more than piece of information of same type.

I suspect we haven't been told the full picture.

N 56°04'39.16"
E 12°55'05.25"