Remove char in a string - 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

Remove char in a string

Author

Topic

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2011-10-19 : 04:21:07

Guys,

I have this records. How would I remove the first 10 char and the last 2 char in a string. can you please complete this scripts. thanks.

Select substring(t1.sku,11,15)as model ????

SKU
-----------------
P300-1110-C731-U
P300-1110-INCREDIBLE-U
P300-1710-DEVOUR-U
P300-1710-DROID2-U
P300-4410-THUNDRBLT4G-U
P333-4110-8530-U

exact result:
-----------------
C731
INCREDIBLE
DEVOUR
DROID2
THUNDRBLT4G
8530

Thank you in advance.

jov

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-10-19 : 04:22:32

only return anything after the 2nd dash ?

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-19 : 04:24:47

is the format consistent? if yes, you can use


SELECT PARSENAME(REPLACE(SKU,'-','.'),2) AS result FROM table

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-10-19 : 04:32:09

or use fnParseString
from http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=76033


select dbo.fnParseString(-3, '-', col)
from   yourtable

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

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-10-19 : 04:38:51

select replace(replace(sku,left(sku,10),''),right(sku,2),'')

No, you're never too old to Yak'n'Roll if you're too young to die.

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2011-10-19 : 04:40:20

It's great. Thank you very much guys.

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2011-10-19 : 04:57:10

I encountered an error to this part of the script

Into #VPData2

select *
from
(Select t1.sku,
Parsename(replace(SKU,'-','.'),2) AS Model,
t1.ESN,
t3.suppliercode,
row_no = row_number() over (partition by t1.sku, t1.esn order by t2.modifiedDatetime desc)
Into #VPData2 -- got an error.
From #VPData as t1 with (nolock)

error message:

Msg 156, Level 15, State 1, Line 11
Incorrect syntax near the keyword 'Into'.
Msg 319, Level 15, State 1, Line 12
Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.
Msg 319, Level 15, State 1, Line 13
Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.
Msg 319, Level 15, State 1, Line 16
Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.

Thanks.

jov

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-19 : 05:03:16

it should be


select * Into #VPData2 
from
(Select t1.sku,
Parsename(replace(SKU,'-','.'),2) AS Model,
t1.ESN,
t3.suppliercode, 
row_no = row_number() over (partition by t1.sku, t1.esn order by t2.modifiedDatetime desc)
From #VPData as t1 with (nolock)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-10-19 : 05:04:54

remove the top select from . .


Select t1.sku,
Parsename(replace(SKU,'-','.'),2) AS Model,
t1.ESN,
t3.suppliercode,
row_no = row_number() over (partition by t1.sku, t1.esn order by t2.modifiedDatetime desc)
Into #VPData2 -- got an error.
From #VPData as t1 with (nolock)

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

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2011-10-19 : 05:16:12

Again thank you guys for the help.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-19 : 05:36:11

wc

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-10-21 : 03:11:38

If the format is fixed, you can also use

declare @s varchar(100)
set @s='P300-4410-THUNDRBLT4G-U'
select SUBSTRING(@s,11,len(@s)-12)

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources