take 1 part of a collumn - 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)

take 1 part of a collumn

Author

Topic

IBoonZ
Yak Posting Veteran

53 Posts

Posted - 2009-04-08 : 09:02:20

hi

I have following data in a collumn

name =
FFF-02 testdata.

But i need only this in another collumn

Name
FFF02
how do i take the '-' away and the text behind it.

TY

ams006
Yak Posting Veteran

55 Posts

Posted - 2009-04-08 : 09:06:20

Will the data in the column always have 3 characters, then a hyphen (-) and then numbers and text or does it vary?

IBoonZ
Yak Posting Veteran

53 Posts

Posted - 2009-04-08 : 09:10:23

it is not always the same

you can have

AAA-03 test 1
ABC-04 test 2
FFF-05 test 3

I only need

AAA03
ABC04
FFF05

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-08 : 09:15:39

SELECT STUFF(SUBSTRING(Col1, 1, 6), 4, 1, '')
FROM Table1

E 12°55'05.63"
N 56°04'39.26"

IBoonZ
Yak Posting Veteran

53 Posts

Posted - 2009-04-08 : 09:19:46

i get an error that the function requires 3 arguments.
the col1, i need to type also, or smth else?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-04-08 : 09:33:08

If it has always one hyphen

SELECT REPLACE(Col1,'-', '') as Col1
FROM Table1

Madhivanan

Failing to plan is Planning to fail

IBoonZ
Yak Posting Veteran

53 Posts

Posted - 2009-04-08 : 09:33:45

still doesnt go :/ incorrect syntax near ,
it is the one after the ')'.

select (substring(article,1,6),4,1,'')
from table1

is what i do (article = collumn name)

IBoonZ
Yak Posting Veteran

53 Posts

Posted - 2009-04-08 : 09:37:47

SELECT REPLACE(Col1,'-', '') as Col1
FROM Table1

works like a charm ty :)

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-04-08 : 09:42:04

select (substring(article,1,6),4,1,'')
from table1

should be

select stuff(substring(article,1,6),4,1,'')
from table1

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources