Solved

SQL UDF to get last word in string

Posted on 2011-03-10
2
941 Views
Last Modified: 2012-05-11
I got the code below off the internet but the syntax is off.. What is the best way to create a UDF that pulls the last word out of a string?
CREATE FUNCTION dbo.ufn.Utility_Last_Word(@myString VARCHAR(4000) )
 RETURNS VARCHAR(4000)
 
RETURN
WITH find_last_blank(pos) AS (
VALUES LENGTH(@myString)
UNION ALL
SELECT pos - 1
  FROM find_last_blank
 WHERE pos > 0
   AND SUBSTR(in_string, pos, 1) <> ' '
)
SELECT SUBSTR(in_string, MIN(pos) + 1)
  FROM find_last_blank
;

Open in new window

0
Comment
Question by:cheryl9063
2 Comments
 
LVL 12

Accepted Solution

by:
mcv22 earned 500 total points
ID: 35099291
CREATE FUNCTION dbo.ufn_Utility_Last_Word(@myString VARCHAR(4000) )
RETURNS VARCHAR(4000)
AS
BEGIN
RETURN
	RIGHT
	(
		@myString, 
		CASE CHARINDEX(' ', REVERSE(@myString))
			WHEN 0 THEN LEN(@myString)
			ELSE CHARINDEX(' ', REVERSE(@myString)) - 1
		END
	)
END;

Open in new window

0
 
LVL 1

Author Closing Comment

by:cheryl9063
ID: 35099312
Thanks!
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

808 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question