How to Parse Data? - 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)

How to Parse Data?

Author

Topic

SCHEMA
Posting Yak Master

192 Posts

Posted - 2010-07-15 : 15:36:23

I have column in this format:

E:\abc\Files\R1\1993-010170 Burman Cadman Services, Inc. DBA Processing Checklist.pdf

E:\abc\Files\R1\1993-010170 Burman Cadman Services, Inc. DBA Checklist.pdf

I Want to Parse it:

Expected Output in following table and columns:

Room Year UNumber Name DocType
R1 1993 010170 Burman Cadman Services, Inc. Checklists

SCHEMA
Posting Yak Master

192 Posts

Posted - 2010-07-16 : 00:07:03

Any help.

Sachin.Nand

2937 Posts

Posted - 2010-07-16 : 02:19:40

Is the format going to be constant?

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

SCHEMA
Posting Yak Master

192 Posts

Posted - 2010-07-16 : 10:13:23

Yes the format will be consistent.

For Ex: in this text:
Burman Cadman Services, Inc. DBA Processing Checklist.pdf

I want to split Company with pdf File.

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2010-07-16 : 11:22:11

[code]
declare @cheesy varchar(max)
declare @cheesy2 varchar(max)
declare @Room varchar(max)
declare @Year varchar(max)
declare @UNumber varchar(max)
declare @Name varchar(max)
declare @DocType varchar(max)

create table meow(Room varchar(100) null, [Year] INT null, UNumber varchar(50) null, Name varchar(50) null, DocType varchar(50) null)
SET @cheesy = 'E:\abc\Files\R1\1993-010170 Burman Cadman Services, Inc. DBA Processing Checklist.pdf'
SET @cheesy2 = 'E:\abc\Files\R1\1993-010170 Burman Cadman Services, Inc. DBA Checklist.pdf'

SET @cheesy = REPLACE(@cheesy,'E:\abc\Files\','')
PRINT @cheesy
SET @Room = REPLACE(SUBSTRING(@cheesy,0,CHARINDEX('\',@cheesy,0)),'\','')
PRINT @Room
SET @cheesy = REPLACE(@cheesy,@Room,'')
SET @cheesy = REPLACE(@cheesy,'\','')
PRINT @cheesy
SET @Year = SUBSTRING(@cheesy,CHARINDEX('\',@cheesy,0),CHARINDEX('-',@cheesy))
PRINT @Year
SET @cheesy = REPLACE(@cheesy,@Year,'')
PRINT @cheesy
SET @UNumber = SUBSTRING(@cheesy,CHARINDEX('-',@cheesy,0),CHARINDEX(' ',@cheesy))
SET @UNumber = REPLACE(@UNumber,'-','')
PRINT @UNumber
SET @cheesy = REPLACE(@cheesy,@UNumber,'')
SET @cheesy = REPLACE(@cheesy,'-','')
PRINT @cheesy
SET @Name = SUBSTRING(@cheesy,0,CHARINDEX('DBA',@cheesy,0))
PRINT @Name
SET @cheesy = REPLACE(@cheesy,@Name,'')
PRINT @cheesy
SET @DocType = SUBSTRING(@cheesy,0,CHARINDEX('.pdf',@cheesy,0))
PRINT @DocType
--Room Year UNumber Name DocType
--R1 1993 010170 Burman Cadman Services, Inc. Checklists
INSERT INTO meow
VALUES(@Room, @Year, @UNumber, @Name, @DocType)
Select * from meow
drop table meow
[/code]

If you don't have the passion to help people, you have no passion

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources