Reusable Variable

Question

Post reply

Reusable Variable

John524

SSC-Addicted

Points: 473
More actions
December 23, 2019 at 6:57 pm

#3710096

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

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

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

John524 SSC-Addicted Points: 473 More actions · Answer 1

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: 76985 More actions · Answer 2

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: 101098 More actions · Answer 3

Or this:

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

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

John524 SSC-Addicted Points: 473 More actions · Answer 4

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: 101098 More actions · Answer 5

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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 6

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.

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

John524 SSC-Addicted Points: 473 More actions · Answer 7

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: 1004340 More actions · Answer 8

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.

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

John524 SSC-Addicted Points: 473 More actions · Answer 9

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: 1004340 More actions · Answer 10

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.

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