extracting and modify data from a field

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

extracting and modify data from a field

Author

Topic

chippyles
Yak Posting Veteran

68 Posts

Posted - 2006-06-28 : 14:44:57

I have a column named Product Name that collects exactly that. I am looking to modify the column and display it a little differently through a new column.


Product Name
LINCOLNWELD LA100 COM
LINCOLNWELD LA82
LINCOLNWELD LA75
LINCOLNWELD LA84 (LNS 164)

I would like to have the new column look like this...


New Product Name
LINCOLNWELD LA-100
LINCOLNWELD LA-82
LINCOLNWELD LA-75
LINCOLNWELD LA-84

I do not know where to begin. I also would like the LA100 to be separated with a dash (-).

Thanks!!

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-06-28 : 15:02:35

>> new column
Means a new column (permanent) in a table and add the changed data to that
or just Display it (in the Query results) ?
or something else ?

>> LA100 --> LA-100
Always "LA" as first 2 characters ?
or after first 2 characters, a hiphen ?
or after alphabetic characters, a hiphen ?

>> (LNS 164)
What happened to those in the New Product Name?

Srinika

chippyles
Yak Posting Veteran

68 Posts

Posted - 2006-06-28 : 15:09:30

Column will not be permanent. I will use this in a stored procedure.

It is always LA

I don't want any characters displayed after LA-100. There could be COM, MIL or whatever we want. For my purpose, I do not need it.

Thanks!!

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-06-28 : 15:43:51

Always "LINCOLNWELD" before "LA-nnn" ?
or Anything seperated by space ?

Srinika

chippyles
Yak Posting Veteran

68 Posts

Posted - 2006-06-28 : 15:47:43

Always LINCOLNWELD

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-06-28 : 23:56:01

Try this

declare @table table
(
	Product_Name	varchar(50)
)
insert into @table
select	'LINCOLNWELD LA100 COM'		union all
select	'LINCOLNWELD LA82'		union all
select	'LINCOLNWELD LA75'		union all
select	'LINCOLNWELD LA84 (LNS 164)'

select	Product_Name, 
	-- part_1, part_2, 
	part_1 + coalesce(nullif(left(part_2, charindex(' ', part_2)), ''), part_2) as [New Product Name]
from
(
    select   Product_Name, 
             left(Product_Name, charindex(' ', Product_Name)) as [part_1],
             stuff(substring(Product_Name, charindex(' ', Product_Name) + 1, len(Product_Name)), 3, 0, '-') as [part_2]
    from     @table
) a

chippyles
Yak Posting Veteran

68 Posts

Posted - 2006-06-29 : 08:16:58

works great. I am having a hard time embedding this into my code. Below is the existing code. Can you please help embed your code into it? If you scroll to the bottom of this message you will see the actual code I need help with. I need your code to create the NEW PRODUCT NAME within my code.

Hopefully all of the rest of the code allows you to see exactly where I get my data

Thanks!!

[CODE]
--CREATE dbo.tM_QA_Specifications and INSERT Data
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tM_QA_Specifications]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tM_QA_Specifications]
GO

CREATE TABLE [dbo].[tM_QA_Specifications] (
[Edono] [nvarchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Usage] [nvarchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Nominal Diameter] [nvarchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Product Name] [nvarchar] (35) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

INSERT INTO dbo.tM_QA_Specifications([Edono],[Nominal Diameter],[Product Name])
SELECT 'ED010999','TEST','LINCOLNWELD LA100 COM' UNION ALL
SELECT 'ED011002','TEST','LINCOLNWELD LA100 COM' UNION ALL
SELECT 'ED031871','TEST','LINCOLNWELD LA84 (LNS 164)' UNION ALL
SELECT 'ED027225','TEST','LINCOLNWELD LA75' UNION ALL
SELECT 'ED026959','TEST','LINCOLNWELD LA82'

--CREATE dbo.tM_RD_PN_Specs and INSERT Data
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tM_RD_PN_Specs]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tM_RD_PN_Specs]
GO

CREATE TABLE [dbo].[tM_RD_PN_Specs] (
[PRODNO] [nvarchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Usage] [nvarchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

INSERT INTO [dbo].[tM_RD_PN_Specs]([PRODNO],[USAGE])
SELECT 'ED011002','SCHEDULE H' UNION ALL
SELECT 'ED027225','SCHEDULE H' UNION ALL
SELECT 'ED031871','SCHEDULE H' UNION ALL
SELECT 'ED026959','SCHEDULE H' UNION ALL
SELECT 'ED010999','SCHEDULE H'

--CREATE vM_RD_PN_SpecS
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[vM_RD_PN_Specs]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[vM_RD_PN_Specs]
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE VIEW dbo.vM_RD_PN_Specs
AS
SELECT dbo.tM_QA_Specifications.Edono, dbo.tM_QA_Specifications.[Nominal Diameter],
dbo.tM_QA_Specifications.[Product Name], dbo.tM_RD_PN_Specs.[Usage]
FROM dbo.tM_QA_Specifications INNER JOIN
dbo.tM_RD_PN_Specs ON dbo.tM_QA_Specifications.Edono = dbo.tM_RD_PN_Specs.PRODNO

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

--CODE I AM HAVING TROUBLE WITH
SELECT TOP 100 PERCENT dbo.tM_QA_Specifications.Edono,
dbo.tM_QA_Specifications.[Nominal Diameter],
dbo.tM_QA_Specifications.[Product Name]
FROM dbo.tM_QA_Specifications INNER JOIN dbo.vM_RD_PN_Specs ON dbo.tM_QA_Specifications.Edono = dbo.vM_RD_PN_Specs.Edono
ORDER BY dbo.tM_QA_Specifications.Edono
[/CODE]

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-06-29 : 09:58:51

Here's my effort (and a suggestion for the view you're having trouble with)...

--data
declare @table table
(
	Product_Name	varchar(50)
)
insert into @table
select	'LINCOLNWELD LA100 COM'		union all
select	'LINCOLNWELD LA82'		union all
select	'LINCOLNWELD LA75'		union all
select	'LINCOLNWELD LA84 (LNS 164)'

--calculation
select Product_Name,
    left(stuff(Product_Name, 15, 0, '-'), charindex(' ', Product_Name + ' ', 15)) as [New Product Name]
from @table

/*results
Product_Name                                       New Product Name                                    
-------------------------------------------------- --------------------------------------------------- 
LINCOLNWELD LA100 COM                              LINCOLNWELD LA-100
LINCOLNWELD LA82                                   LINCOLNWELD LA-82
LINCOLNWELD LA75                                   LINCOLNWELD LA-75
LINCOLNWELD LA84 (LNS 164)                         LINCOLNWELD LA-84
*/

/* Your view?
SELECT     TOP 100 PERCENT dbo.tM_QA_Specifications.Edono, 
                           dbo.tM_QA_Specifications.[Nominal Diameter], 
                           dbo.tM_QA_Specifications.[Product Name],
    left(stuff(dbo.tM_QA_Specifications.[Product Name], 15, 0, '-'), charindex(' ', dbo.tM_QA_Specifications.[Product Name] + ' ', 15)) as [New Product Name]
FROM         dbo.tM_QA_Specifications INNER JOIN dbo.vM_RD_PN_Specs ON dbo.tM_QA_Specifications.Edono = dbo.vM_RD_PN_Specs.Edono
ORDER BY dbo.tM_QA_Specifications.Edono
*/

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

chippyles
Yak Posting Veteran

68 Posts

Posted - 2006-06-29 : 10:20:43

Thanks worked great. I had to add a CASE to it, but it worked great.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources