Removed Unneccesary Character in a fields

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

Removed Unneccesary Character in a fields

Author

Topic

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2011-09-05 : 01:13:07

Guys,

How could be removed the unneccesary character or data from the column fields.
I got this when i copy the records from Bulk insert.
I couldnt figure out how to use the script in removing this records.

I tried to use this using this commands but some Itemnumber is incomplete.
Right(vp.Itemnumber,16)

Here is the sample:

TABLE1
Create ItemNumber nvarchar(255)
-----------------------------------------------------------------------
Insert Values(Phone power on but vibrate is too weak P300-3110-VX8575124)
Insert Values(place a test call. P300-1710-DROID125)
Insert Values(Valuespower issues.touchscreen not working. P333-4410-EVO-U126)
Insert Values(TRACKBALL NOT WORKING P300-4110-TOUR127)
Insert Values(VERIFIELD DISPLA ISSUE P300-1710-DROID129)
Insert Values(working correctly. P300-3131-VX9200MAR128)
Insert Values(Within 48 hours<br>//Technical - Software<br>// ????? phone froze up. P300-1710-DROIDX)
Insert Values(Freezes P300-4110-BOLD)

Results
--------------------
Item Number
--------------------
P300-3110-VX8575
P300-1710-DROID
P333-4410-EVO-U
P300-4110-TOUR
P300-1710-DROID
P300-3131-VX9200MAR
P300-1710-DROIDX
P300-4110-BOLD

Thanks,
Jov

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-09-05 : 01:43:47

[code]
SELECT right(ItemNumber, charindex(' ', reverse(ItemNumber )) - 1)
[/code]

KH
[spoiler]Time is always against us[/spoiler]

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2011-09-05 : 04:17:45

I got an error:

Msg 536, Level 16, State 3, Line 1
Invalid length parameter passed to the RIGHT function.

Right(vp.ItemNumber, charindex(' ', reverse(vp.ItemNumber )) - 1)

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-09-05 : 04:24:38

[code]
SELECT right(ItemNumber, charindex(' ', reverse(ItemNumber ) + ' ') - 1)
[/code]

KH
[spoiler]Time is always against us[/spoiler]

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2011-09-05 : 04:45:22

Thank you very much. khtan..its working.
Btw, another one. can be?
I have to get the latest records based on date2.
but im always getting the 2 records. this could not be group also because they have diff. price.
I need to get the second record.

TABLE1
Sku ESN
P333-4110-8350-U 010001046926820

TABLE2
Sku------------- ESN --------Price---Date1-----------Price2--Date2-----------Date3
P333-4110-8350-U 010001046926820 223.36 12/3/2010 107.12 12/9/2010 11/17/2010
P333-4110-8350-U 010001046926820 223.36 12/3/2010 125.87 1/3/2011 12/9/2010

Regards,

Jonel

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-09-05 : 04:47:08

[code]
select *
from
(
select t2.*, row_no = row_number() over (partition by t1.sku, t1.esn order by t2.date2 desc)
from table1 t1 inner join table2 t2 on t1.sku = t2.sku and t1.esn = t2.esn
) t
where t.row_no = 1
[/code]

KH
[spoiler]Time is always against us[/spoiler]

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2011-09-05 : 05:15:40

Hi Khtan,

By the way my reference here is itemnumber/Sku and ReceivingPO/PONumber

TABLE1
Sku--------------ESN---------------------Price---RecevingPO
P245-0110-M860AWS-U 268435459212833173 223.36 8933867

TABLE2
ItemId------------------PONumber-Price--Date1-----------Price2--Date2-----------Date3
P245-0110-M860AWS-U 8933867 223.36 12/3/2010 107.12 12/9/2010 11/17/2010
P245-0110-M860AWS-U 8933867 223.36 12/3/2010 125.87 1/3/2011 12/9/2010

Here is my Codes:

Select
vp.Sku,
vp.ESN,
vp.Price1,
vp.ReceivingPO,
vp.ReceivedDate,
sa.Price2,
Datename(Month, sa.ConfirmedDLV) As POMonth,
From #TABLE1 as vp with (nolock)
Left Outer Join TABLE2 as sa with (nolock)
On vp.ReceivingPO = sa.POnumber COLLATE Chinese_Taiwan_Stroke_CI_AS
and vp.sku = sa.itemid COLLATE Chinese_Taiwan_Stroke_CI_AS
Group by
vp.Sku,
vp.ESN,
vp.Price1,
vp.ReceivingPO,
vp.ReceivedDate,
sa.Price2,
Datename(Month, sa.ConfirmedDLV)
Order by sku

Thanks Again.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-09-05 : 05:25:04

just change the column name accordingly


select *
from
(
    select t2.*, row_no = row_number() over (partition by t1.sku, t1.esn order by t2.date2 desc)
    from   table1 t1 inner join table2 t2 on t1.sku = t2.ItemId and t1.RecevingPO = t2.PONumber
) t
where t.row_no = 1

KH
[spoiler]Time is always against us[/spoiler]

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2011-09-05 : 05:46:02

Okay. what happen if only one records in t2.? thanks.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-09-05 : 05:48:45

that record will still be included in the result

KH
[spoiler]Time is always against us[/spoiler]

Villanuev
Constraint Violating Yak Guru

478 Posts