Data Clean - 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 2000 Forums

Transact-SQL (2000)

Data Clean

Author

Topic

KabirPatel
Yak Posting Veteran

54 Posts

Posted - 2007-09-21 : 09:03:43

I have data in the following format:

DocID DocName
1 ____________________test.doc
2 __________fred.doc
3 _______________________________bogus.doc

I need to strip out the underscores at the beginning of the DocName.

i.e. I need the data in the following format:

DocID DocName
1 test.doc
2 fred.doc
3 bogus.doc

What would be the best way to do this?

Thanks in advance,
Kabir

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-09-21 : 09:07:09

The simple way is

Select DocId, replace(DocName,'_','') as DocName from yourTable

Note that it would replace all occurances of _ , not neccessarily the leading

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2007-09-21 : 09:09:50

[code]
DECLARE @SomeData TABLE
(
DocID int,
DocName varchar(50)
)

INSERT INTO @SomeData
SELECT 1, '____________________test.doc' UNION ALL
SELECT 2, '__________fred.doc' UNION ALL
SELECT 3, '_______________________________bogus.doc'

SELECT DocID, DocName, RIGHT(DocName, DATALENGTH(DocName) + 1 - PATINDEX('%[^_]%', DocName))
FROM @SomeData
[/code]
Kristen

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2007-09-21 : 09:10:01

DECLARE @tbl TABLE (Col1 varchar(100))

INSERT INTO @tbl
select
'____________________test.doc'

union all select
'__________fred.doc'
union all select
'_______________________________bogus.doc'

select right(col1,charindex('_',REVERSE(col1))-1)
from @tbl

Jim

Kristen
Test

22859 Posts

Posted - 2007-09-21 : 09:12:01

REVERSE won't work with:

union all select
'__________fre_d.doc'

Kristen

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2007-09-21 : 09:14:14

Nice and simple Madhivanan! The more complex my solution the less I understood the problem.

Jim

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-09-21 : 09:15:14

quote:
Originally posted by jimf

DECLARE @tbl TABLE (Col1 varchar(100))

INSERT INTO @tbl
select
'____________________test.doc'

union all select
'__________fred.doc'
union all select
'_______________________________bogus.doc'

select right(col1,charindex('_',REVERSE(col1))-1)
from @tbl

Jim

First row in the result is blank

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-09-21 : 09:16:51

quote:
Originally posted by jimf

Nice and simple Madhivanan! The more complex my solution the less I understood the problem.

Jim

Yes. As long as the data are in the format ______xxxx, my solution is simple

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources