Reusable Variable

  • John524

    SSC-Addicted

    Points: 448

    hello,

    I know there's gotta be a way here, but I'm struggling.  Let's say I have a last name parsing sql statement (using the first few letters) but the rules are complex in the event of hyphenated names, Sr's Jr's etc...  Anyway, I have all that sorted out using SELECT CASE.  This works great, but here is my question.  I have a stored procedure that returns several fields all using this same statement.  I'd like to be able to simplify this so I'd just need to declare it once at the top and then use the name (variable or function... whatever you guys think)

    I tried DECLARE @lName varchar(50) SET @lName =LEFT(lastName,5)   and then...

    SELECT lastName,@lName FROM myTable but it returned an error

    obviously, I'm over simplifying what I need to do here, but I'm sure you get the idea.  Would a function be in order here?

    thank you!  I love this forum

     

  • John524

    SSC-Addicted

    Points: 448

    Just an update...

    I was able to get this to work nicely in a function, but could a variable accomplish the same thing?

  • drew.allen

    SSC Guru

    Points: 76737

    You oversimplified to the point where you did not provide enough information to answer the question, so, no, we don't get the idea.  I suspect that you should be using a CROSS APPLY with a table value constructor.

    SELECT lastName, lName
    FROM myTable
    CROSS APPLY( VALUES(LEFT(lastname, 5)) ) l(lName);

    Drew

    PS: If you get an error, you should almost always include the text of the error message.

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • ScottPletcher

    SSC Guru

    Points: 98441

    Or this:

    SELECT lastName, LEFT(lastName, 5) AS lName FROM myTable

    SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

  • John524

    SSC-Addicted

    Points: 448

    Yes sorry.... I have a tendency of assuming folks can read my mind.  What I want to do is to get the first 5 letters of the first name and then the first 5 letters of the last name.  The field I need this info from is a field named fullName.  There are myriad variations in this field... juniors, hyphenated etc....  Now as I said this works great in a very long CASE statement.  Problem is, I use this in several different places, so if we ever need to modify the code (say to pull the first 4 letters instead) we'd need to change it in all instances.  As I said, I was able to get this to work great in a user defined function, but I was asked by a coworker, if we could accomplish this declaring a variable at the beginning.

    thank you!

    by the way, I'm not familiar with the CROSS APPLY you referred to... sounds interesting

  • ScottPletcher

    SSC Guru

    Points: 98441

    That's an extraordinarily complex task when you really look into the more difficult cases, with honorifics (Dr., etc.), suffixes (III, IV) and so on.

    I would do this process one time, as soon as possible after a row is first loaded storing the results.  You could even store them in another table if you preferred.

    I would avoid re-doing the split every time it was read, because: 1) the overhead is too high and 2) you might get a different result, either from changed code or different versions of the parsing code if you attempt to do it live each time.

    SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

  • Jeff Moden

    SSC Guru

    Points: 996661

    John524 wrote:

    Yes sorry.... I have a tendency of assuming folks can read my mind.  What I want to do is to get the first 5 letters of the first name and then the first 5 letters of the last name.  The field I need this info from is a field named fullName.  There are myriad variations in this field... juniors, hyphenated etc....  Now as I said this works great in a very long CASE statement.  Problem is, I use this in several different places, so if we ever need to modify the code (say to pull the first 4 letters instead) we'd need to change it in all instances.  As I said, I was able to get this to work great in a user defined function, but I was asked by a coworker, if we could accomplish this declaring a variable at the beginning.

    thank you!

    by the way, I'm not familiar with the CROSS APPLY you referred to... sounds interesting

    If you're not familiar with CROSS APPLY, there's also a pretty good chance that you're not familiar with iTVFs (Inline Table Valued Functions), which usually blow the door off of Scalar User Defined Functions.

    My recommendation is that you post the code for your function and let us have a look at it for you.  Considering that a lot of people don't have such a function (even if it turns out to be Scalar rather than an iTVF), lot's of people will learn something new and we might be able to help you make it run faster (if it needs such a thing). 😀

    --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".
    "If "pre-optimization" is the root of all evil, then what does the resulting no optimization lead to?"

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

  • John524

    SSC-Addicted

    Points: 448

    Sure, here we are (funky as it is)  So long story short, I need to combine the First 5 characters of the Last Name with the first 3 characters of the First Name. The gist is pretty simple. For Last Name it's everything up to the first comma, then for First Name I concatenate with the first word after the first comma and up to the first space.  I then use LEFT(dbo.fnParseLastName,5) + LEFT(dbo.fnParseFirstName,3)  Example Johnson, William B would be JohnsWil.  I needed to deal with the short last names as you see below to handle names like ie… Lis, Joseph X would be LisJos and not Lis, Jos

    Anyway, these are the functions I'm using and they work well for my purposes, but I'd like to see this alternative TVF thing...

    thanks again!

    CREATE FUNCTION [dbo].[fnParseFirstName] (@CustomerName varchar(100))
    RETURNS varchar(100)
    AS
    BEGIN
    DECLARE @fName varchar(100)
    SET @fName =
    CASE WHEN CHARINDEX(' ',SUBSTRING(@CustomerName,CHARINDEX(',',@CustomerName,1)+2,1000),1)=0
    THEN
    SUBSTRING(@CustomerName,CHARINDEX(',',@CustomerName,1)+2,1000)
    ELSE

    SUBSTRING(@CustomerName,CHARINDEX(',',@CustomerName,1)+2,CHARINDEX(' ',SUBSTRING(@CustomerName,CHARINDEX(',',@CustomerName,1)+2,1000),1))
    END
    RETURN @fName
    END
    CREATE FUNCTION [dbo].[fnParseLastName](@CustomerName varchar(100))
    RETURNS varchar(100)
    AS
    BEGIN
    DECLARE @lName varchar(100)
    SET @lName = case when
    right(
    SUBSTRING(@CustomerName,1, CASE WHEN CHARINDEX(' ',@CustomerName) = 0 then LEN(@CustomerName) ELSE CHARINDEX(' ',@CustomerName)-1 end),1) = ',' then

    --modify in the event there is a comma
    SUBSTRING(RTRIM(SUBSTRING(@CustomerName,1, CASE WHEN CHARINDEX(' ',@CustomerName) = 0 then LEN(@CustomerName)
    ELSE CHARINDEX(' ',@CustomerName)-1 end)),1,LEN(RTRIM(SUBSTRING(@CustomerName,1, CASE WHEN CHARINDEX(' ',@CustomerName) = 0 then LEN(@CustomerName)
    ELSE CHARINDEX(' ',@CustomerName)-1 end)))-1)
    else

    --no, there isn't a comma
    SUBSTRING(@CustomerName,1, CASE WHEN CHARINDEX(' ',@CustomerName) = 0 then LEN(@CustomerName)
    ELSE CHARINDEX(' ',@CustomerName)-1 end)
    end
    RETURN @lName
    END
  • Jeff Moden

    SSC Guru

    Points: 996661

    Do you have any names that look like the following and, if so, what do you want to do with them?  Eliminate the periods and stop at the first space for the first name (not including the space after the comma).

    Berry, Jo L

    Brown, Jo

    Brown, Jo A

    Dudenhoefer, Ed

    Dudenhoefer, Ed R

    Leonetti, A. Francesca

    Meadows, Ed

    Saravan, K.

    Valentine, H. Brian

    Wright, A. Scott

    Yong, Y. L.

    Zimmerman, Jo J.

    --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".
    "If "pre-optimization" is the root of all evil, then what does the resulting no optimization lead to?"

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

  • John524

    SSC-Addicted

    Points: 448

    Hi Again (and Merry Christmas),

    Actually, the data I was working with (<500 records) had absolutely no first names <3 characters, so to be brutally honest, I didn't give it a second thought.  Anyway, I tried 'Ed' (Jones, Ed X) and it worked fine.  However, I tried Wong, W L and it left a blank space after the W.  (I C&P'd the values into Excel and did a LEN( ) and discovered this) so, as long as there will never be first names less than 2 characters, it works, but obviously I'll need to deal with this.  I'm thinking I'll have to do something similar with what I did with the fnParseLastName function (which I probably should have done from the get-go)  But to answer your question, yes... I'd like to eliminate the periods and stop at the first space after the first comma (not including the initial space after the first comma)

    thank you for bringing this to my attention

  • Jeff Moden

    SSC Guru

    Points: 996661

    Merry Christmas to you, as well.  I thought I'd be about the only one in the world to be posting today. 😀

    Thanks for the feedback on the name-splitter function.  I believe there may be a way to help both in the problems we've just discussed and turning this into a high performance iTVF.  It may not be today, though.

    --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".
    "If "pre-optimization" is the root of all evil, then what does the resulting no optimization lead to?"

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

Viewing 11 posts - 1 through 11 (of 11 total)

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