May 17, 2006 at 5:40 am
Hi All !
I have a table A with fields f_name, m_name,l_name, initials.
Suppose I have William Terence Simpson in f_name,m_name and l_name respectively. Now i have to insert the initial of these three columns in ascending order into the initials column i.e. 'STW' should be inserted into the initials column.
Pls suggest me the most efficient way of doing the above insert.
Thanx in advance !
Rajesh
May 17, 2006 at 6:56 am
Depands on a lot of things (and not sure there is a 'most efficient')
Is this a one off?
Single insert or Multiple Inserts?
Via Procedure?
Via Trigger?
One way to derrive the initials is use case statments
CASE
WHEN LEFT(f_name,1) < LEFT(m_name,1) AND LEFT(f_name,1) < LEFT(l_name,1) THEN LEFT(f_name,1)
WHEN LEFT(m_name,1) < LEFT(l_name,1) THEN LEFT(m_name,1)
ELSE LEFT(l_name,1)
END +
CASE
WHEN LEFT(f_name,1) > LEFT(m_name,1) AND LEFT(f_name,1) < LEFT(l_name,1) THEN LEFT(f_name,1)
WHEN LEFT(f_name,1) < LEFT(m_name,1) AND LEFT(f_name,1) > LEFT(l_name,1) THEN LEFT(f_name,1)
WHEN LEFT(m_name,1) > LEFT(l_name,1) THEN LEFT(m_name,1)
ELSE LEFT(l_name,1)
END +
CASE
WHEN LEFT(f_name,1) > LEFT(m_name,1) AND LEFT(f_name,1) > LEFT(l_name,1) THEN LEFT(f_name,1)
WHEN LEFT(m_name,1) > LEFT(l_name,1) THEN LEFT(m_name,1)
ELSE LEFT(l_name,1)
END
however will have some trouble if names missing
Far away is close at hand in the images of elsewhere.
Anon.
May 18, 2006 at 1:11 am
Try:
SELECT UPPER(ISNULL(LEFT(f_name,1),'-')
+ISNULL(LEFT(m_name,1),'-')
+ISNULL(LEFT(l_name,1),'-')) AS Initials
FROM MyTable
ORDER BY Initials
<edited>
Andy
May 18, 2006 at 1:33 am
It gives the initials but it doesnt sort them in ascending order .
Anyway Thanks for the reply !
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply