How to Removed unwanted CHR

[jovillanuev]

Hi Guys,

There are unwanted character in my columns. this character is came from the bulk insert.
How tdo i removed this character using scripts.

Please see sample below.

ItemCode--Item Number
----------------------------------------------------------
123--Phone power on but vibrate is too weak  P300-3110-VX8575
124--place a test call.  P300-1710-DROID
125--power issues.touchscreen not working.  P333-4410-EVO-U
126--TRACKBALL NOT WORKING  P300-4110-TOUR
127--VERIFIELD DISPLA ISSUE  P300-1710-DROID
129--working correctly.  P300-3131-VX9200MAR
128-- - Within 48 hours//Technical - Software// ?????  phone froze up.  P300-1710-DROIDX
222--Freezes  P300-4110-BOLD


Results
--------------------
ItemCode--Item Number
--------------------
123--P300-3110-VX8575
124--P300-1710-DROID
125--P333-4410-EVO-U
126--P300-4110-TOUR
127--P300-1710-DROID
129--P300-3131-VX9200MAR
128--P300-1710-DROIDX
222--P300-4110-BOLD

Thank you in Advance.

Jov

[debasisdas]

those are not characters but strings on data.

On what basic / logic you want the output ?

Is the format always same for all the records ?

[jovillanuev]

Sorry. Yes strings from Data.
I nned the output like the result i was given.
I need the string from the right side where the result is like start from P300, P333 and so on.

Results should be like this.
--------------------
ItemCode--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
[/CODE]

Thanks.

Jov

[debasisdas]

but i don't see any consistency in the data, neither length nor format.

[jovillanuev]

What do you mean. Is there any possibilities to get the data.
Example:

Phone power on but vibrate is too weak P300-3110-VX8575

the result should be like this: P300-3110-VX8575

[debasisdas]

Take these following 2 items from you data as example

123--Phone power on but vibrate is too weak P300-3110-VX8575
124--place a test call. P300-1710-DROID

Line 1-----length----60---length of Item Number-----16-----starting position of Item Number--45
Line 2-----length----39---length of Item Number-----15-----starting position of Item Number--25

So, the data is inconsistent.

It will be difficult to design a generic logic for this kind of inconsistent data.

[jovillanuev]

Thank you guys for the reply.

Select Right(ItemNumber, charindex(' ', reverse(ItemNumber ) + ' ') - 1)

Jov.