THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

SELECT Hints, Tips, Tricks FROM Hugo Kornelis WHERE RDBMS = 'SQL Server'

Just stuff it!

This blog has moved! You can find this content at the following new location:

Published Thursday, October 12, 2006 8:50 PM by Hugo Kornelis

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS



Roji Thomas said:

October 13, 2006 6:05 AM

Denis the SQL Menace said:

-- Version 5, using PARSENAME
LTRIM(PARSENAME(REPLACE(FullName,',','.'),1))AS FirstName
FROM   BadData

October 13, 2006 10:08 AM

Denis the SQL Menace said:


I didn't see the second part (3 names) before, here is a solution for that too by using PARSENAME

select PARSENAME(FullName,NameLen+1) LastName,
PARSENAME(FullName,NameLen) FirstName,
COALESCE(PARSENAME(FullName,NameLen-1),'') as MiddleInitial
select len(FullName) -len(replace(FullName,',','')) as NameLen,REPLACE(REPLACE(FullName,'.','~'),', ','.') as FullName
from BadData) x
October 13, 2006 10:23 AM

Denis the SQL Menace said:

Argh, who put the i next to the o

Hugo I meant to say Hugo not hugi, please don't call me Denise now  ;-)

October 13, 2006 10:27 AM

Denis the SQL Menace said:

Small problem, I forgot to replace ~ with . in the result

Here it is, final version (I promise)

SELECT PARSENAME(FullName,NameLen+1) AS LastName,
PARSENAME(FullName,NameLen) AS FirstName,
COALESCE(REPLACE(PARSENAME(FullName,NameLen-1),'~','.'),'') AS MiddleInitial
SELECT LEN(FullName) -LEN(REPLACE(FullName,',','')) AS NameLen,
REPLACE(REPLACE(FullName,'.','~'),', ','.') AS FullName
FROM BadData) x

316 characters (according to edit plus)
October 13, 2006 10:40 AM

Peter W. DeBetta said:

Nice touch using ParseName. I'm jealous I didn't think of it first!

Worth metioning, however, is that you're are limited to a 4 items being parsed. ParseName can take a string with the following formats - 'a', 'a.b', 'a.b.c', 'a.b.c.d' - but cannot parse 'a.b.c.d.e'

So for a name with only three parts (last, first, middle) it works great, but for a 5 part name (prefix/title, first, middle, last, suffix) such as 'Dr., John, Q., Public, III', the technique could no longer be used, as Parsename returns NULL if the string being parsed has more than four items.

October 13, 2006 11:34 AM

Denis the SQL Menace said:

Peter, you can't change requirements on me, this is not work  ;-)

But you are right,  PARSENAME is used to split (SERVERNAME,DBNAME, ObjectOwner, Object)

Quite handy with IP adresses also

for 5 part we can always do a join with a number table, something like

DECLARE @chvGroupNumbers VARCHAR(1000)
SELECT @chvGroupNumbers ='1,4,77,88,4546,234,2,3,54,87,9,6,4,36,6,9,9,6,4,4,68,9,0,5,3,2,'

SELECT SUBSTRING(',' + @chvGroupNumbers + ',', NumberID + 1,
CHARINDEX(',', ',' + @chvGroupNumbers + ',', NumberID + 1) - NumberID -1)AS Value
FROM NumberPivot
WHERE NumberID <= LEN(',' + @chvGroupNumbers + ',') - 1
AND SUBSTRING(',' + @chvGroupNumbers + ',', NumberID, 1) = ','

Or make a UDF

October 13, 2006 11:44 AM

Hugo Kornelis said:

> please don't call me Denise now

I won't, Denos. :-P

Nice solution using PARSENAME - kudos!

I also liked the extended version using a numbers table, but this one can be simplified to
SELECT SUBSTRING(@chvGroupNumbers + ',',
                CHARINDEX(',', @chvGroupNumbers + ',', Number + 1) - Number) AS Value
FROM   dbo.Numbers
WHERE  Number <= LEN(@chvGroupNumbers + ',')
AND    SUBSTRING(',' + @chvGroupNumbers + ',', Number, 1) = ','

But of course, you'll have to add a ROW_NUMBER() to preserve the order of the elements, otherwise we'd end up switching first and last names or day and month numbers and looking all American <g>

SELECT SUBSTRING(@chvGroupNumbers + ',',
                CHARINDEX(',', @chvGroupNumbers + ',', Number + 1) - Number) AS Value,
      ROW_NUMBER() OVER (ORDER BY Number) AS Position
FROM   dbo.Numbers
WHERE  Number <= LEN(@chvGroupNumbers + ',')
AND    SUBSTRING(',' + @chvGroupNumbers + ',', Number, 1) = ','
October 14, 2006 1:01 PM

Daniel Lins said:

ok hugo, here s one more use of stuff (and PATINDEX) .

if there is a need to remove a character of defined length at the end of a string ,you could put to use the following:

lets say @str = someone and something and

select stuff(@str,PATINDEX('%and',@str),3,'')

here the result would be


'someone and something'

cool uh?!

December 13, 2007 8:07 AM

Daniel Lins said:

Now guys .......anyone willing to help...............its kinda simple........but my head is kinda here s the question.................

I need to make a report in the following manner below

year | RG1 | RG2            | value

07     Plan    Executed        3

07     Plan    Abandoned     1

07     Plan    Complete       1

this is to be done using sql 2000 (wihtout making use of reporting service)

So who's got the answer????

December 13, 2007 8:19 AM

Hugo Kornelis said:

Hi Daniels,

Sorry for the late reply. This is quite an old post, so I missed the new comment.

To answer your question, I need to know what the data you start with looks like. So I suggest that you post CREATE TABLE statements for the tbales involved (with all constraints, properties, and indexes included), INSERT statements with some well-chosen rows of sample data, and expected output.

However, don't post it here. Post it to a newsgroup, for instance microsoft.public.sqlserver.programming. There are many people in that group, always willing to help you solve such tasks, so you'll get a reply much sooner than when you post here! :)

Happy holidays!

December 24, 2007 9:57 AM

Ronald Beuker said:

Great article Hugo, thx! There's just a small error: POSINDEX should be CHARINDEX :-)

May 25, 2008 12:33 PM

Leave a Comment


About Hugo Kornelis

Hugo is co-founder and R&D lead of perFact BV, a Dutch company that strives to improve analysis methods and to develop computer-aided tools that will generate completely functional applications from the analysis deliverable. The chosen platform for this development is SQL Server. In his spare time, Hugo likes to visit the SQL Server newsgroups, in order to share and enhance his knowledge of SQL Server.
Privacy Statement