Parse strings to get select output

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

Parse strings to get select output

Author

Topic

s_anr
Yak Posting Veteran

81 Posts

Posted - 2009-11-26 : 10:16:10

Hi i have a field with following value :

USE TESTTABLE
GO
CREATE TABLE Example (
Description varchar(500))
GO
INSERT INTO [TESTTABLE].[dbo].[Example] ([Description])
VALUES ('hello This is a test query. Thank you.
Please be advised of the following :
Name: Doe,John
ID: 123456
Preferred Name: None
Type: My type
Class: My Class
Last Day of Year: 31-Dec-2009
Domain: MYDomain
Username: My Username
Email: my@email.com
Contact: Jane,Mary')
GO
INSERT INTO [TESTTABLE].[dbo].[Example] ([Description])
VALUES ('hello This is a test query. Thank you.
Please be advised of the following :
Name:Doe, John
ID: 123456 
Preferred Name: None
Type: My type
Class: My Class
Last Day of Year: 31-Dec-2009
Domain: MYDomain
Username: My Username
Email: my@email.com
Contact: Jane,Mary')
GO
INSERT INTO [TESTTABLE].[dbo].[Example] ([Description])
VALUES ('hello This is a test query. Thank you.
Please be advised of the following :
Name:    Doe,John  
ID:   123456  
Preferred Name: None
Type: My type
Class: My Class
Last Day of Year: 31-Dec-2009
Domain: MYDomain
Username: My Username
Email: my@email.com
Contact: Jane,Mary')
GO

I want to extract three things from the above data stored ina single field

1. LAST NAME (All characters before COMMA till a special character is met)
2. FIRST NAME (All characters AFTER COMMA till a special character is met)
2. ID (All numbers after the word ID. Ignore all special characters before and after the numbers)
The Output will be :


FIRST NAME         LAST NAME         ID
John               Doe               123456
John               Doe               123456
John               Doe               123456

Please help how can I use substrings to extract this data in SQL 2005

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-11-27 : 01:39:55

http://sqlblogcasts.com/blogs/madhivanan/archive/2009/11/18/parsing-a-string.aspx

Madhivanan

Failing to plan is Planning to fail

s_anr
Yak Posting Veteran

81 Posts

Posted - 2009-11-27 : 02:58:06

The blog was helpdful. Thank you so much.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-11-27 : 04:00:25

quote:
Originally posted by s_anr

The blog was helpdful. Thank you so much.

You are welcome

Madhivanan

Failing to plan is Planning to fail

april198474
Starting Member

11 Posts

Posted - 2009-11-27 : 05:28:58

Charindex will help you, try the following code:

declare @begin1 int, @end1 int, @begin2 int, @end2 int, @begin3 int, @end3 int
declare @des varchar(500), @FirstName varchar(20), @LastName varchar(20), @ID varchar(20)

USE TESTTABLE
GO

create table #Temp
(
FirstName varchar(20),
LastName varchar(20),
ID varchar(20)
)

declare cursorDes cursor for
(select [description] from [TESTTABLE].[dbo].[Example])

open cursorDes

fetch next from cursorDes into @Des

while @@fetch_status = 0
begin

set @begin1 = (select top 1 charindex('Name:', @des)) + 5
set @end1 = (select top 1 charindex(',', @des) ) - @begin1
set @begin2 = @end1 + @begin1 + 1
set @end2 = (select top 1 charindex('ID:', @des)) - @begin2
set @begin3 = (select top 1 charindex('ID:', @des)) + 3
set @end3 = (select top 1 charindex('Preferred Name:', @des)) - @begin3

set @FirstName = (select rtrim(ltrim(substring( @des, @begin1, @end1))))
set @LastName = (select rtrim(ltrim(substring( @des, @begin2, @end2))))
set @ID = (select rtrim(ltrim(substring( @des, @begin3, @end3))))

insert into #Temp([Lastname], [FirstName], [id])
values ( @FirstName, @LastName, @ID )

fetch next from cursorDes into @des

end

select * from #temp

close cursorDes
deallocate cursorDes

drop table #Temp

GO

s_anr
Yak Posting Veteran

81 Posts

Posted - 2009-11-27 : 05:51:31

sorry to say but it still gives the same error when i run it on the actual database.

MuralikrishnaVeera
Posting Yak Master

129 Posts

Posted - 2014-06-05 : 08:07:41

Following may work for you


SELECT * INTO #temp
FROM (
SELECT SUBSTRING([Description],CHARINDEX('Name',[Description]),CHARINDEX('Preferred Name',[Description])-CHARINDEX('Name',[Description])) AS col FROM [TESTTABLE].[dbo].[Example]
)a
SELECT 
		REPLACE(SUBSTRING(col,CHARINDEX(',',Col)+1,CHARINDEX('ID',col)-CHARINDEX(',',Col)-1),' ','') AS FirstName
	  , REPLACE(SUBSTRING(col,CHARINDEX(':',Col)+1,CHARINDEX(',',col)-CHARINDEX(':',Col)-1),' ','') AS LastName
	  , REPLACE(SUBSTRING(col,CHARINDEX('ID',Col)+3,LEN(Col)),' ','') AS ID
FROM #temp

---------------
Murali Krishna

You live only once ..If you do it right once is enough.......

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources