Breaking one field into two - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

Breaking one field into two

Author

Topic

dprichard
Yak Posting Veteran

94 Posts

Posted - 2008-12-04 : 10:51:54

I have a field with a string in it. It is the product name with different sizes. So in one column I have:

Mens Coat Extra Large
Mens Coat Large
Boys Coat Extra Large
Boys Coat Large

For the results I need to show

Mens Coat
- Extra Large
- Large
Boys Coat
- Extra Large
- Large

Thanks for any help on this.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-04 : 11:14:59

will the sizes values be always consistent?

ddamico
Yak Posting Veteran

76 Posts

Posted - 2008-12-04 : 12:34:50

Do you have any primary keys in this table to distinguish on record from another?

dprichard
Yak Posting Veteran

94 Posts

Posted - 2008-12-04 : 12:53:50

Actually, I have been digging around a little more. This is an application the customer users and we are trying to pull some info out. The primary key is ITEMNO, then the Product Name is DESC then it looks like it may have the sizes in a separate field called segments.

So, it looks like what I actually need to do is this: (Sorry I know this has changed)

I have an item number

1001BB003ALG
1001BB003AMD
1001BB003ASM

I need to show it like this

1001BB003
- 1001BB003ALG
- 1001BB003AMD
- 1001BB003ASM

ddamico
Yak Posting Veteran

76 Posts

Posted - 2008-12-04 : 13:18:34

Okay, I think this should do the trick. The assumption here is that the suffix is always the size attribute and its a constant length in your example 3. If not this will need to be adjusted so let me know and I can give you another option but this requires that you know all the possible suffixes for the sizes. Anyhow, here is the solution based on the assumption

DECLARE @x TABLE (
id INT,
a VARCHAR(100),
b VARCHAR(100)
)

INSERT INTO @x (id,a)
SELECT 1,'1001BB003ALG' UNION
SELECT 2,'1001BB003AMD' UNION
SELECT 3,'1001BB003ASM'

--SELECT Y.[id], Y.[a], Z.[suffix]
--, REPLACE(Y.[a],Z.[suffix],'')
UPDATE Y
SET Y.[b] = REPLACE(Y.[a],Z.[suffix],'')
FROM @x Y
INNER JOIN (
SELECT DISTINCT RIGHT(LTRIM(RTRIM(a)),3) AS suffix
FROM @x) Z ON REVERSE(SUBSTRING(REVERSE(Y.[a]),1,3)) = Z.[suffix]

SELECT * FROM @x

Column "b" is where you would put the product code. give this a run see its what u desire.

dprichard
Yak Posting Veteran

94 Posts

Posted - 2008-12-04 : 13:26:53

Is it possible to use replace to find and replace more than one string. I still want to pull in the product name, but want to remove the large, extra large at the end.

REPLACE([DESC], ' Extra Large', '') AS Product

dprichard
Yak Posting Veteran

94 Posts

Posted - 2008-12-04 : 13:30:10

I know I can nest it, is that the best way?

dprichard
Yak Posting Veteran

94 Posts

Posted - 2008-12-04 : 13:35:00

This is how I am doing it now.

SELECT SUBSTRING(FMTITEMNO, 0, 12) AS productcode, REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE([DESC],
' Extra Large', ''), ' Extra Small', ''), ' Youth Small', ''), ' Youth', ''), ' Small', ''), ' Medium', ''), ' Extra Large', ''), ' Large', '') AS Product
FROM ICITEM
GROUP BY SUBSTRING(FMTITEMNO, 0, 12), REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE([DESC], ' Extra Large', ''),
' Extra Small', ''), ' Youth Small', ''), ' Youth', ''), ' Small', ''), ' Medium', ''), ' Extra Large', ''), ' Large', '')

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources