trim - 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

General SQL Server Forums

New to SQL Server Programming

trim

Author

Topic

a.ashabi
Posting Yak Master

117 Posts

Posted - 2008-08-15 : 13:02:30

Hi.I have a field name which is product name & the values are like "Variety 42 - 1.9 oz (54 g) [5 lb (2.27 kg)]"
I mean some of the values have parentheses & some have both parentheses & brackets.
How can I trim the end part of the field: parentheses & brackets
which in this example would be (54 g) [5 lb (2.27 kg)]
thanks.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-15 : 13:08:46

Didnt get that. do you mean removing ( & ] then use REPLACE. or you want to remove text inside them as well? then use SUBSTRING and CHARINDEX

a.ashabi
Posting Yak Master

117 Posts

Posted - 2008-08-15 : 13:17:30

No.I need to get the extra part of the product name:
if the product name is : Variety 42 - 1.9 oz (54 g) [5 lb (2.27 kg)]
I need only the:(54 g) [5 lb (2.27 kg)]

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-08-15 : 13:20:21

What are other examples? will the desired section ALWAYS begin with the first occurance of "(" or "[" in the string?

Be One with the Optimizer
TG

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-15 : 13:22:03

quote:
Originally posted by a.ashabi

No.I need to get the extra part of the product name:
if the product name is : Variety 42 - 1.9 oz (54 g) [5 lb (2.27 kg)]
I need only the:(54 g) [5 lb (2.27 kg)]

will the data will be always consistent? will it always be of format

sometext - numeric value oz (xx g) [y lb (z kg)]

a.ashabi
Posting Yak Master

117 Posts

Posted - 2008-08-15 : 14:35:39

I have all combination values in this feild.these are some examples:

170 caplets
Grape 2 lbs (920 g)
Variety 42 - 1.9 oz (54 g) [5 lb (2.27 kg)]

the first one is OK.but I need to get the (920 g) from the second one & (54 g) [5 lb (2.27 kg)]
from the third one.

a.ashabi
Posting Yak Master

117 Posts

Posted - 2008-08-15 : 14:35:39

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-15 : 14:43:00

quote:
Originally posted by a.ashabi

I have all combination values in this feild.these are some examples:

170 caplets
Grape 2 lbs (920 g)
Variety 42 - 1.9 oz (54 g) [5 lb (2.27 kg)]

the first one is OK.but I need to get the (920 g) from the second one & (54 g) [5 lb (2.27 kg)]
from the third one.

try this

SELECT SUBSTRING(yourfield,CHARINDEX('(',yourfield)+1,LEN(yourfield)) FROm YourTable

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-08-15 : 14:43:56

or this:


select right(product, len(product)- charindex('(', product)+1)
from   (
       select 'Variety 42 - 1.9 oz (54 g) [5 lb (2.27 kg)]'  as product union all
       select '170 caplets' union all
       select 'Grape 2 lbs (920 g)' 
       ) as YourTable


output:
(54 g) [5 lb (2.27 kg)]
170 caplets
(920 g)

Be One with the Optimizer
TG

a.ashabi
Posting Yak Master

117 Posts

Posted - 2008-08-15 : 15:05:13

thanks a lot.worked :)

a.ashabi
Posting Yak Master

117 Posts

Posted - 2008-08-15 : 16:47:18

sorry Im new on SQL.

SELECT product_name, SUBSTRING(product_name,CHARINDEX('(',product_name),LEN(product_name)) From tbl_product

worked fine.but now I want to put the result of this Query to update a field name.
i've tried :

UPDATE tbl_product
SET product_display_measure = SUBSTRING(product_name,CHARINDEX('(',product_name),LEN(product_name))

but gives me :String or binary data would be truncated.
The statement has been terminated.

what should I do?
thanks

a.ashabi
Posting Yak Master

117 Posts

Posted - 2008-08-15 : 17:07:07

I have also tried:

UPDATE [druginfo].[dbo].[tbl_product2]
set product_display_measure = (SELECT SUBSTRING(product_name,CHARINDEX('(',product_name),LEN(product_name)) )
From tbl_product
where product_active=1

but the error is:Ambiguous column name 'product_name'

:(

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-16 : 01:02:34

quote:
Originally posted by a.ashabi

sorry Im new on SQL.

SELECT product_name, SUBSTRING(product_name,CHARINDEX('(',product_name),LEN(product_name)) From tbl_product

worked fine.but now I want to put the result of this Query to update a field name.
i've tried :

UPDATE tbl_product
SET product_display_measure = SUBSTRING(product_name,CHARINDEX('(',product_name),LEN(product_name))

but gives me :String or binary data would be truncated.
The statement has been terminated.

what should I do?
thanks

this means your destination field product_display_measure doesnt have enough length to hold the value coming. so you need to increase the length of product_display_measure field use
ALTER TABLE ALTER COLUMN .... for altering the length

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-16 : 01:06:37

quote:
Originally posted by a.ashabi

I have also tried:

UPDATE p2
set p2.product_display_measure = SUBSTRING(p1.product_name,CHARINDEX('(',p1.product_name)+1,LEN(p1.product_name))
From [druginfo].[dbo].[tbl_product2] p2
join tbl_product p1
on p1.relatedcol=p2.relatedcol
where p1.product_active=1

but the error is:Ambiguous column name 'product_name'

:(

you need to alises to distinguish as you've same column in both tables.Make sure you change length as suggested before even if you want to try this

relatedcol is column by which two tables are related.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources