March 16, 2011 at 6:27 am
hi,
i have data like
1234.45.1.46.678.1
i want a function which return when I Passesd 1 it will return the 1234
2 it will return the 45
3 it will return the 1
4 it will return the 46
-------
7 it will return null
what is procedure use char index in this function
March 16, 2011 at 6:32 am
have a look at the SubString Function it should do what you want.
March 16, 2011 at 6:41 am
What is the purpose of this function and how will it be used?
March 16, 2011 at 7:21 am
hi ,can any help with code or query
March 16, 2011 at 7:33 am
Perhaps you would be courteous enough to answer Lynn's question?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
March 16, 2011 at 7:37 am
for my project purpose
March 16, 2011 at 7:47 am
SUPER SQL STAR (3/16/2011)
for my project purpose
That's it? You expect others to take the time to craft a technical response to a question when you are not even prepared to spend some of your own time explaining yourself?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
March 16, 2011 at 7:48 am
Your project, okay. I could provide you with code, but I'm not going to bother.
What you need is a delimited string parse function. Do a search of this site and you should find something usable, maybe even my rountine which I have provided in several threads.
Good luck.
March 16, 2011 at 7:54 am
Lynn Pettis (3/16/2011)
Your project, okay. I could provide you with code, but I'm not going to bother.What you need is a delimited string parse function. Do a search of this site and you should find something usable, maybe even my rountine which I have provided in several threads.
Good luck.
Lynn i did this on the side for my own edu-macation; i did it the "hard" way first, with CHARINDEX2 functions and chopping up the string, what a pain, but valuable as far as learning, i guess.
then i did it with a certain delimiting function you are mentioning; i changed it to be scalar instead of a table function, since i wanted to emulate the parse name function in another thread, it works so much better and is much clear code to deploy. I'm keeping that in my snippets
it's too bad the OP want's to be recalcitrant, he could have benefited a lot by providing all the volunteers here with the information they requested.
Lowell
March 16, 2011 at 8:33 am
Phil Parkin (3/16/2011)
SUPER SQL STAR (3/16/2011)
for my project purposeThat's it? You expect others to take the time to craft a technical response to a question when you are not even prepared to spend some of your own time explaining yourself?
I TRIED AND THEN POST IN THE FORUM.
March 16, 2011 at 9:23 am
Lowell (3/16/2011)
Lynn Pettis (3/16/2011)
Your project, okay. I could provide you with code, but I'm not going to bother.What you need is a delimited string parse function. Do a search of this site and you should find something usable, maybe even my rountine which I have provided in several threads.
Good luck.
Lynn i did this on the side for my own edu-macation; i did it the "hard" way first, with CHARINDEX2 functions and chopping up the string, what a pain, but valuable as far as learning, i guess.
then i did it with a certain delimiting function you are mentioning; i changed it to be scalar instead of a table function, since i wanted to emulate the parse name function in another thread, it works so much better and is much clear code to deploy. I'm keeping that in my snippets
it's too bad the OP want's to be recalcitrant, he could have benefited a lot by providing all the volunteers here with the information they requested.
Have you compared the performance of scalar function vs the in-line TVF against a million row table? Once you do that you see why I went with the in-kine TVF vs the scalar function.
Also, thank you for the feedback, it is nice to know that others appreciate the work that many of us have put in helping here.
March 16, 2011 at 12:05 pm
Lynn on my roughly half a million row table i created with the data below, i get roughly double the performance with ITV vs scalar;16 seconds vs 9 seconds.
from experience,I know if i increase the rows tested, the ITV will perform better and better.:
for example, doubling the data at ~900M rows, the performance is 34 seconds vs 15 seconds.
Thank you again my friend. proving to yourself that something is faster beats taking someone elses word for it.
--443556 rows on my master database
SELECT
CONVERT(varchar,ABS(CHECKSUM( NEWID() )% 0xFF))
+ '.' + CONVERT(varchar,ABS(CHECKSUM( NEWID() )% 0xFF))
+ '.' + CONVERT(varchar,ABS(CHECKSUM( NEWID() )% 0xFF))
+ '.' + CONVERT(varchar,ABS(CHECKSUM( NEWID() )% 0xFF)) AS IP
into #tmp
FROM sys.columns c1
cross apply sys.columns c2
Lowell
March 16, 2011 at 12:21 pm
Check out my blog (link below). I have a post where I compare hardcoding a function vs in-line tvf vs scalar function.
March 16, 2011 at 7:17 pm
SUPER SQL STAR (3/16/2011)
Phil Parkin (3/16/2011)
SUPER SQL STAR (3/16/2011)
for my project purposeThat's it? You expect others to take the time to craft a technical response to a question when you are not even prepared to spend some of your own time explaining yourself?
I TRIED AND THEN POST IN THE FORUM.
First, don't yell. Second, I thought about responding when I read this at work, but decided that trying to do so from my BlackBerry just wouldn't allow me to express myself properly (plus time allows emotion to calm down).
Reading between the lines here, you say that you took the time explaining yourself and then posted in the forum. Sorry, but I don't see enough in any of your posts to truely provide you with valueable help. Sure, I could just throw you some code, but how does that help you if you don't understand it, or worse, it doesn't suit your purpose?
If you truely want help from us (the volunteers who give up our own time) then you need to help us. We can't see what you see, we don't know what you know about your problem or what you have done in trying to solve it. We need as much information as possible from you if we are going to help you and guide you.
Plus, and there is a plus, in return for your extra effort to ensure we understand your problem and what you are trying to accomplish you will receive tested code in return.
The first thing you need to do is read the first article in my signature block regarding asking for help. Follow the instructs it gives and you are more of the way there. The only other thing we ask for is that you also provide the expected results based on the sample data that you provide. Oh, we do ask for something else; sample data, not real data from your production system. Don't post anything here that you wouldn't want your competitors or customers to see.
I hope this helps when you come back in the future looking for help.
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply