StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>If you cannot use computed columns (the recommendation by John was to actually change the definition of your table to make fname and lname computed columns (persisted) would be even better. You can then populate fullname and not have to worry about dealing with fname and lname.</p> <p>e.g.</p> <pre><code>use tempdb go create table dbo.Customers( id INT PRIMARY KEY CLUSTERED, fname as LTRIM(RTRIM(SUBSTRING(fullname, 0, CHARINDEX(' ', fullname)))) persisted, lname as LTRIM(RTRIM(SUBSTRING(fullname, CHARINDEX(' ', fullname)+1, 8000))) persisted, fullname varchar(512) ); insert into dbo.Customers (Id,fullname) values(1,'Amie Dann'); insert into dbo.Customers (Id,fullname) values(2,'John Hamm'); insert into dbo.Customers (Id,fullname) values(3,'Charlie Key'); select * from dbo.Customers </code></pre> <p>Here is a trigger example, notice that i used a instead of trigger so you do not have to perform an an insert/update twice.</p> <pre><code>use tempdb go create table dbo.Customers( id INT PRIMARY KEY CLUSTERED, fname varchar(256), lname varchar(256), fullname varchar(512) ); CREATE TRIGGER dbo.trg_dbo_Customers on dbo.Customers instead of insert,update AS begin IF exists(select 1 from deleted) BEGIN UPDATE c SET fullname = i.fullname, fname = LTRIM(RTRIM(SUBSTRING(i.fullname, 0, CHARINDEX(' ', i.fullname)))), lname = LTRIM(RTRIM(SUBSTRING(i.fullname, CHARINDEX(' ', i.fullname)+1, 8000))) FROM dbo.Customers c INNER JOIN inserted i ON i.id = c.id INNER JOIN deleted d ON i.id = d.id END ELSE BEGIN INSERT INTO dbo.Customers (id,fullname,fname,lname) SELECT Id = i.Id, fullname = i.fullname, fname = LTRIM(RTRIM(SUBSTRING(i.fullname, 0, CHARINDEX(' ', i.fullname)))), lname = LTRIM(RTRIM(SUBSTRING(i.fullname, CHARINDEX(' ', i.fullname)+1, 8000))) FROM inserted i WHERE not exists(select 1 from dbo.Customers c WHERE i.id = c.id) END END go insert into dbo.Customers (Id,fullname) values(1,'Amie Dann'); insert into dbo.Customers (Id,fullname) values(2,'John Hamm'); insert into dbo.Customers (Id,fullname) values(3,'Charlie Key'); select * from dbo.Customers </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload