Pulling out a substring - 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)

Pulling out a substring

Author

Topic

icw
Constraint Violating Yak Guru

378 Posts

Posted - 2010-03-11 : 05:36:47

Hi My field will contain a value like '234 HDTF Lond 230310'
This signifies course no = 234
Course code = HDTF
Location = London
date = 23rd march 2010 (uk date)
Sometimes it might be only a two character course code or course number e.g. '65 HP Birm 280110'

I need to pull out the Course code and the dates
I have been experimenting with charindex and can pull out the code if it starts at the 5th character with the following query.

SUBSTRING(contsupref, 5, NULLIF(CHARINDEX(' ', contsupref) - 1, -1)) AS [CourseCode]

But this doesn't work if it starts at the 4th Character (as in the 2nd example)

In terms of getting teh date I know I can use the CAST function but not sure how to search for the body of six numbers to make the date.

Thanks a lot if you can help

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-03-11 : 05:42:22

[code]
select dbo.fnParseString(-1, ' ', '234 HDTF Lond 230310'),
dbo.fnParseString(-2, ' ', '234 HDTF Lond 230310'),
dbo.fnParseString(-3, ' ', '234 HDTF Lond 230310'),
dbo.fnParseString(-4, ' ', '234 HDTF Lond 230310')
[/code]

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

icw
Constraint Violating Yak Guru

378 Posts

Posted - 2010-03-11 : 06:57:41

Sorry I don't understand

My TSQL skills are fairly limited compared to most of you guys.

Could you explain? How to use this and what it does?
and will it work in SQL 2000?

Thanks a lot

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-03-11 : 07:15:41

Yes. It will works in SQL 2000.

1. click on the fnParseString link
2. copy and paste the function into your query window to create the function

to use the function, see the example in my prev post

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

icw
Constraint Violating Yak Guru

378 Posts

Posted - 2010-03-11 : 07:25:22

thanks - I copied the past and tried to execute it but got the following error.

Server: Msg 409, Level 16, State 1, Procedure fnParseString, Line 16
The assignment operator operation cannot take a text data type as an argument.
Server: Msg 8116, Level 16, State 1, Procedure fnParseString, Line 16
Argument data type text is invalid for argument 1 of reverse function.

icw
Constraint Violating Yak Guru

378 Posts

Posted - 2010-03-11 : 07:30:50

Update - I changed the TEXT to varchar and it worked (i.e. it created the function)

However I still am very confused as to what it is doing
Below is the result for the query you gave.

2 NULL NULL NULL

I don't understand what this is telling me.

How do I use this for finding my starting position (i.e. Just after the first space?)
Thanks

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-03-11 : 08:01:06

quote:
Originally posted by icw

Update - I changed the TEXT to varchar and it worked (i.e. it created the function)

However I still am very confused as to what it is doing
Below is the result for the query you gave.

2 NULL NULL NULL

I don't understand what this is telling me.

How do I use this for finding my starting position (i.e. Just after the first space?)
Thanks

Post the query you used

Madhivanan

Failing to plan is Planning to fail

icw
Constraint Violating Yak Guru

378 Posts

Posted - 2010-03-11 : 08:07:39

Hi

I only meant i ran the following. in order to test if the function worked.

select dbo.fnParseString(-1, ' ', '234 HDTF Lond 230310'),
dbo.fnParseString(-2, ' ', '234 HDTF Lond 230310'),
dbo.fnParseString(-3, ' ', '234 HDTF Lond 230310'),
dbo.fnParseString(-4, ' ', '234 HDTF Lond 230310')

The problem is I need to undetsrand what this does or how to use it to help me

Thanks

icw
Constraint Violating Yak Guru

378 Posts

Posted - 2010-03-11 : 08:09:18

My Field is called contsupref and i'd really like to be able to pull out a course code and a date from it.
If it makes it easier there are always 4 blocks of data as you can see from the original post.
the 2nd block is the course code and the 4th block is the date.

Hope that makes it easier and not more complicated:O)

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-03-11 : 08:26:46

quote:
Originally posted by icw

thanks - I copied the past and tried to execute it but got the following error.

Server: Msg 409, Level 16, State 1, Procedure fnParseString, Line 16
The assignment operator operation cannot take a text data type as an argument.
Server: Msg 8116, Level 16, State 1, Procedure fnParseString, Line 16
Argument data type text is invalid for argument 1 of reverse function.

for SQL 2000, you can use varchar(8000) instead of TEXT. You must just declare it as varchar.


CREATE FUNCTION dbo.fnParseString
(
	@Section SMALLINT,
	@Delimiter CHAR,
	@Text varchar(8000)
)

quote:
Originally posted by icw

Update - I changed the TEXT to varchar and it worked (i.e. it created the function)

However I still am very confused as to what it is doing
Below is the result for the query you gave.

2 NULL NULL NULL

I don't understand what this is telling me.

How do I use this for finding my starting position (i.e. Just after the first space?)
Thanks

That is because you didn't define any size for the varchar. Change to the above

And with that, you should be able to extract individual word from the string using the function


select	course_no 	= dbo.fnParseString(-1, ' ', '234 HDTF Lond 230310'),
	course_code	= dbo.fnParseString(-2, ' ', '234 HDTF Lond 230310'),
	location	= dbo.fnParseString(-3, ' ', '234 HDTF Lond 230310'),
	[date]		= dbo.fnParseString(-4, ' ', '234 HDTF Lond 230310')

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

icw
Constraint Violating Yak Guru

378 Posts

Posted - 2010-03-11 : 08:59:49

WOW!! That's brilliant

Thanks so much for that.
You're a genius!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-03-11 : 09:06:28

You are welcome