September 18, 2014 at 2:24 pm
A column of a table has values in the format - 35106;#Grandbouche-Cropp, Amy
I need to format the column data in such a way that only the text after # (Grandbouche-Cropp, Amy) remain in the column.
The text before ;# (35106) should be inserted in to another column of the same table.
Below is the table structure:
create table [HR_DEV_DM].[CFQ_TEST].sp_CFQ_Commercial_Referrals
(
ID int identity,
PromotionalCode nvarchar(4000),
QuoteNumber nvarchar(100),
CreatedBy nvarchar(100),
Created datetime,
ModifiedBy nvarchar(100),
Modified datetime,
CreatedBy_SalesRepSharePointID int,
ModifiedBy_ModBySharePointID int
)
Please help....
September 18, 2014 at 2:30 pm
The column CreatedBy has this value and the numeric value needs to be inserted into the column CreatedBy_SalesRepSharePointID.
September 18, 2014 at 4:24 pm
You should use a computed column if possible. If not, you can still take the conversion code from below and use it in an UPDATE:
ALTER TABLE [HR_DEV_DM].[CFQ_TEST].sp_CFQ_Commercial_Referrals
ADD CreatedBy_SalesRepSharePointID AS CAST(LEFT(CreatedBy, CHARINDEX(';', CreatedBy + ';') - 1) AS int)
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply