Solved

SQL UDF to get last word in string

Posted on 2011-03-10
2
933 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

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Suggested Solutions

Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

707 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now