CODEWITHSUNDEEP
t-sqlsql-server-2005selectstored-procedures
TimN_FL
TimN_FL

Reputation: 67

T-SQL Parse text in select statement

I am currently working on a project to import data from one table to another. I am trying to parse a field that contains a FULLNAME into its parts LAST,FIRST,MI. The names are all in the format of "LAST,FIRST MI" I have written a stored procedure that correctly parses and returns the results as neccessary but I am unsure as to how to encorporate the stored procedure into a single select statement. For instance, current I have:

SELECT FULLNAME From UserInfo

and what I would like to have is something like this:

SELECT Last, First, MI from UserInfo

Currently my stored procedure takes the form of ParseName(FULLNAME, Last as OUTPUT, First as OUTPUT, MI as OUTPUT). How can I call this procedure and have the output variables split into 3 different columns?

Upvotes: 2

Views: 2110

Answers (3)

Josien
Josien

Reputation: 13877

You could put the results of your stored procedure in a (temporary) table, like this (I added the FULLNAME column to provide a join condition, you would have to adapt your stored procedure to do that):

CREATE TABLE #temp (
  FULLNAME NVARCHAR(..)
  ,Last NVARCHAR(..)
  ,First NVARCHAR(..)
  ,MI NVARCHAR(..)
);

INSERT INTO #temp (Last, First, MI)
EXECUTE MySproc;

If you want to be able to execute SELECT Last, First, MI from UserInfo structurally, you'd have to first add three columns to UserInfo for your name information, and then insert the parsed data that you got from your stored procedure.

EDIT

You mention that you use a SELECT ... INTO ... to put the data in a new table. I'm guessing that the new table does not have the FULLNAME column, and then you would be better off using a table valued function (as this answer suggests). If you keep the FULLNAME column however, you can use that to join the temp table with your new table to update the new table as follows:

UPDATE NUI
  SET NUI.Last = T.Last, NUI.First = T.First, NUI.MI = T.MI
  FROM NewUserInfo AS NUI
  INNER JOIN #temp AS T ON NUI.FULLNAME = T.FULLNAME;

You could use this UPDATE method also with another join condition if you do not have the FULLNAME column in your new table, but make sure you run a good test beforehand to check if the join holds.

Hope this helps, good luck!

Upvotes: 1

kristof
kristof

Reputation: 53834

Replace your stored procedure with table valued function. You can then apply this function to all the rows.

Below is an example - just put your logic for parsing the name

create FUNCTION dbo.f_parseName(@inFullName varchar(255))
RETURNS 
    @tbl TABLE (lastName varchar(255), firstName varchar(255), middleName varchar(255))
as
BEGIN
-- put your logic here
insert into @tbl(lastName,firstName,middleName)
select substring(@inFullName,0,10),substring(@inFullName,11,10), substring(@inFullName,21,10)

return

end

apply the function

-- sample data
declare @fullNames table (fullName varchar(255))
insert into @fullNames (fullName) values
('111111111122222222223333333333')
,('AAAAAAAAAABBBBBBBBBBCCCCCCCCCC')


select 
    fn.fullName
    ,pn.lastName
    ,pn.firstName
    ,pn.middleName
from 
    @fullNames fn
    cross apply dbo.f_parseName(fn.fullName) pn

Upvotes: 1

Johan
Johan

Reputation: 1192

You could add computed columns to table like this:

alter table UserInfo
    add firstName as SUBSTRING(fullName, CHARINDEX(',',fullName,0)+2, LEN(fullName)-CHARINDEX(',',fullName,0)-CHARINDEX(' ', REVERSE(fullName),0)-1)
        ,lastName as SUBSTRING(fullName, 0, CHARINDEX(',',fullName,0))
        ,middleInitital as REVERSE(SUBSTRING(REVERSE(fullName),0,CHARINDEX(' ', REVERSE(fullName),0)))

But the best solution would be to do the other way around. Normalize the data with real columns for firstName, lastName and middleInitial and do a computed column for the fullName.

The expressions in the code above may need a little more work, as I am sure they can be written more effectivly. I only made them work to show the idea.

After creating the computed columns you may do this:

select  firstName
        ,lastName
        ,middleInitital
from UserInfo

Upvotes: 0

Related Questions