Results 1 to 13 of 13
  1. #1
    Join Date
    Mar 2003
    Posts
    223

    Unanswered: How to read third word from a string?

    Hallow, everyone:

    I want to read third word from a string. For example, the string is,

    ABCDE fghijk 031 LPN OPQ

    I need 031. How to read it?

    Any help will be appreciated.

    Thanks

    ZYT

  2. #2
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    Ok, do I win a prize for finding the ugliest, most cumbersome way to do this?

    Code:
    declare @strtest as varchar(100)
    set @strtest='ABCDE fghijk 031 LPN OPQ'
    select substring(@strtest,charindex(' ', @strtest, charindex(' ',@strTest,1)+1), charindex(' ', @strtest,charindex(' ', @strtest, charindex(' ',@strTest,1)+1)+1)-charindex(' ', @strtest, charindex(' ',@strTest,1)+1))
    Of course, it crashes if there are only three words to begin with.
    Inspiration Through Fermentation

  3. #3
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    I'm sure there is an easier way..but here's my offering

    Code:
    
    DECLARE @x varchar(8000)
    
    SET @x = 'ABCDE fghijk 031 LPN OPQ'
    
    SELECT SUBSTRING(@x
    	,  CHARINDEX(' ',@x,CHARINDEX(' ',@x)+1)+1
    	, (CHARINDEX(' ',@x,CHARINDEX(' ',@x,CHARINDEX(' ',@x)+1)+1)-1) 
    	- (CHARINDEX(' ',@x,CHARINDEX(' ',@x)+1)+1)+1)
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  4. #4
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    I think you would have to add a space to the end of the string, just to make sure it always works, even if there are only 3 words.
    Inspiration Through Fermentation

  5. #5
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    parsing strings is so much easier in compiled code. forgive me for saying this but sql sucks at it. what sql is good at is set based operations.

    you should do this on the client if you can:

    string[] s = mystring.Split(' ');
    string third = s.Length >= 3 ? s[2] : null;
    Last edited by jezemine; 02-06-07 at 13:16.

  6. #6
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    that is what is listed in Inside T-SQL Programming as one of the justifications for implementing CLR and I looked over the performance metrics and played with it myself. but I am still of the mind that if it requires C# code do it in the application. we are prepping for the whole 2K5 thing, and one the things that scares me is the potential abuse that CLR leaves open. in sql 2k, i had to worry about bad sql which I could fix. Now I might have to worry about bad C# in my sql server process and it has been too long since I really programmed app code.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  7. #7
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    Quote Originally Posted by Thrasymachus
    that is what is listed in Inside T-SQL Programming as one of the justifications for implementing CLR and I looked over the performance metrics and played with it myself. but I am still of the mind that if it requires C# code do it in the application. we are prepping for the whole 2K5 thing, and one the things that scares me is the potential abuse that CLR leaves open. in sql 2k, i had to worry about bad sql which I could fix. Now I might have to worry about bad C# in my sql server process and it has been too long since I really programmed app code.
    any technology can be abused. that's not necessarily a reason not to use it though. it can be a very powerful thing for stuff that sql is not designed for, such as string parsing or complicated math.

    if you are not comfortable with C# or some other .net language though, I wouldn't recommend using CLR in sql server. why not learn it though? it's always good to grow.

  8. #8
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Here's so more abuse

    Code:
    CREATE FUNCTION udf_GetWord (
    	  @str nvarchar(4000)
    	, @Word int
    	, @Delim char(1)
    )
    RETURNS nvarchar(4000)
    AS
    BEGIN
    	DECLARE @LastPosition int, @DelimFound int, @Start int, @End int, @WordFound nvarchar(4000)
    
    	SELECT @LastPosition = 0, @DelimFound = 0
    	WHILE (@DelimFound < @Word-1)
    	  BEGIN
    		IF (CHARINDEX(@Delim, @str, @LastPosition + 1) = 0)
    			BREAK
    	
    		ELSE
    		  BEGIN
    			SET @LastPosition = CHARINDEX(@Delim, @str, @LastPosition + 1)
    			SET @DelimFound = @DelimFound + 1
    		  END
    		
    	  END
    	
    	SET @Start = @LastPosition + 1
    	SET @End = CHARINDEX(@Delim, @str, @LastPosition + 1) - @Start
    	IF @End < 0 SET @End = LEN(@str)-@start+1
    	SELECT @WordFound = SUBSTRING(@str,@start,@end)
    	RETURN @WordFound
    END
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  9. #9
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    Quote Originally Posted by jezemine
    any technology can be abused. that's not necessarily a reason not to use it though. it can be a very powerful thing for stuff that sql is not designed for, such as string parsing or complicated math.

    if you are not comfortable with C# or some other .net language though, I wouldn't recommend using CLR in sql server. why not learn it though? it's always good to grow.
    the average dba makes more than the average developer and no one is going to pay me to code C# right now. I actually spent the first few years of my programming career as a vb \ java \ classic asp developer but it has been all dba for a while now. lets see, should I take the entry level c# developer job with a paycut or should I take the good paying mid-level\senior dba job? hmmmmmm. I am only into my 2nd sql 2k5 book, so I am far enough behind in what I am decent at.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  10. #10
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    I wasn't suggesting that you quit and start fresh as a dime-a-dozen programmer. just that you could learn C# if you wanted to. sounds like you don't want to though.

    in that case stick with parsing strings in sql. it works, it's just not the most efficient way.

  11. #11
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    It's a front end issue...let the minions handle it
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  12. #12
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    Quote Originally Posted by jezemine
    I wasn't suggesting that you quit and start fresh as a dime-a-dozen programmer. just that you could learn C# if you wanted to. sounds like you don't want to though.

    in that case stick with parsing strings in sql. it works, it's just not the most efficient way.
    I hear you but the only way to get good is to do it everyday and I just do not have that opp. I actually picked up Inside C# and got myself a compiler last summer and frankly I got bored with it.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  13. #13
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    fair enough.

    about getting bored though, i think that's your own fault (or maybe the books fault - I hardly ever read tech books), not the fault of the language. there are lots of interesting and fun things you can do in C/C++/C#.

    Here's just one tiny example: implement an RDBMS. your beloved SQL Server is implemented in those languages.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •