Results 1 to 13 of 13
  1. #1
    Join Date
    Oct 2003
    Posts
    4

    Unanswered: mysql function in mssql?

    I'm trying to make a mysql query work in mssql, but my problem is that I can't find any function in mssql that is comparable to the field() function in mysql. I'm using the function as follows...

    SELECT Index from table WHERE Index IN (arrayofindices) ORDER BY FIELD(Index, arrayofindices)

    any suggestions?
    Last edited by jellomania; 10-20-03 at 16:01.

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322

    Re: mysql function in mssql?

    Originally posted by jellomania
    any suggestions?
    Sure...why don't you tell us what FIELD() does...
    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.

  3. #3
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322

    Re: mysql function in mssql?

    Found it...

    FIELD(str,str1,str2,str3,...)
    Returns the index of str in the str1, str2, str3, ... list. Returns 0 if str is not found. FIELD() is the complement of ELT():
    mysql> SELECT FIELD('ej', 'Hej', 'ej', 'Heja', 'hej', 'foo');
    -> 2
    mysql> SELECT FIELD('fo', 'Hej', 'ej', 'Heja', 'hej', 'foo');
    -> 0
    Pretty cool...you need to write a udf
    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
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    If I understand correctly, you could do this with a case statement:

    ORDER BY CASE INDEX
    WHEN 1 then FieldA
    WHEN 2 then FieldB
    .
    .
    .

    blindman

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322

    Re: mysql function in mssql?

    Actually, look at CHARINDEX but it returns the position in the string, not the offset...

    This returns how many words...but you can modify it

    Code:
    CREATE FUNCTION udf_WORDS
    	(@str varchar(8000), @Del char(1)=' ')
    RETURNS int
    AS
    
    /* Mimic REXX function */
    BEGIN
    	DECLARE @Words INT, @Pos INT, @x Int
    	SELECT @Words = 0, @Pos = 1, @x = -1
    
    	WHILE (@x <> 0)
    		BEGIN
    			SET @x = CHARINDEX(@Del, @str, @Pos)
    			SET @Pos = @x + 1
    			SET @Words = @Words + 1
    		END
    
    	RETURN @Words
    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.

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Originally posted by blindman
    If I understand correctly, you could do this with a case statement:

    ORDER BY CASE INDEX
    WHEN 1 then FieldA
    WHEN 2 then FieldB
    .
    .
    .

    blindman
    But I think then data to be evaluated is in a string, delimited by space...
    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.

  7. #7
    Join Date
    Oct 2003
    Posts
    4

    Re: mysql function in mssql?

    Originally posted by Brett Kaiser
    Sure...why don't you tell us what FIELD() does...
    sorry 'bout that.

    I think I could best explain with an example.

    SELECT Index from table WHERE Index IN (arrayofindices) ORDER BY FIELD(Index, arrayofindices)


    let's say arrayofindices contains the values (4,7,2,3,9,1,8), in that order

    and let's also say that the SELECT statement (without the ORDER BY clause) only returns the values 1,2,3,7,9

    then ORDER BY FIELD(Index, arrayofindices) will return the values (7,2,3,9,1) in this specific order.

    Basically, it orders the query results by the order they fall within the spedified array.

    Thanks!
    Last edited by jellomania; 10-20-03 at 17:56.

  8. #8
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    That is a tough one.

    If your array was a string you could use the CHARINDEX or PATINDEX functions, though you would have to be careful about similar values within your array.

    For a true array, I'd like to know why you want such an odd sort order. Is there not a table already in your database to which you could join your query, rather than using the IN clause? I guess I feel like this problem might be best solved be reevaluating the logic or the database design.

    blindman

  9. #9
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322

    Re: mysql function in mssql?

    Originally posted by jellomania

    let's say arrayofindices contains the values (4,7,2,3,9,1,8), in that order

    and let's also say that the SELECT statement (without the ORDER BY clause) only returns the values 1,2,3,7,9

    then ORDER BY FIELD(Index, arrayofindices) will return the values (7,2,3,1,9) in this specific order.


    Thanks!
    Don't you mean 7,2,3,9,1..or am I missing 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.

  10. #10
    Join Date
    Oct 2003
    Posts
    4

    Re: mysql function in mssql?

    Originally posted by Brett Kaiser
    Don't you mean 7,2,3,9,1..or am I missing it?
    oops.. you would be correct.. thanks for catching that!

  11. #11
    Join Date
    Oct 2003
    Posts
    706

    Re: mysql function in mssql?

    [i]
    SELECT Index from table WHERE Index IN (arrayofindices) ORDER BY FIELD(Index, arrayofindices)
    You'll need to construct the query string appropriately, by hand. For example:
    Code:
    SELECT Index from Table
    WHERE Index IN ('A', 'B', 'C')
    ORDER BY Index
    If you need to search a number of different fields and assemble their results into one output then you might be wandering into the magical land of the UNION query:
    Code:
    SELECT "Field1" as FieldName, Field1 as FieldValue
    FROM Table
    WHERE [i]Field1[i] IN ('A', 'B', 'C')
    UNION
    SELECT ...
    ...
    ORDER BY ...
    (parts inserted by code are italicized.)

    Most SQL servers, and both MS-SQL(Sybase) and MySQL are no exception, provide various non-standard extensions to the language which can be used to save time in some cases, at the expense of requiring conversion-work on your part if and when the application is ported elsewhere.

    Also, servers sometimes have unexpected performance-hits, but there might be another way to do exactly the same thing. For example, a UNION query might prove to be expensive while a series of queries that each insert data into a temporary table might be cheap. Anytime you are doing a conversion project, it's important to "fiddle around" with things to find out, as soon as possible, what works well and what doesn't. Be sure to do these things with a honkin' big and ugly (i.e. "realistic!") dataset.
    ChimneySweep(R): fast, automatic
    table repair at a click of the
    mouse! http://www.sundialservices.com

  12. #12
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322

    Re: mysql function in mssql?

    Originally posted by jellomania
    oops.. you would be correct.. thanks for catching that!
    Good...I only thought I was losing my mind...

    How about this...

    Code:
    USE NorthWind
    GO
    
    CREATE TABLE myTable99 (Col1 int, Col2 int)
    GO
    
    INSERT INTO myTable99 (Col1, Col2)
    SELECT 1, 7 UNION ALL
    SELECT 2, 2 UNION ALL
    SELECT 3, 3 UNION ALL
    SELECT 4, 9 UNION ALL
    SELECT 5, 1
    GO
    
        SELECT * 
          FROM Orders 
    INNER JOIN myTable99
    	ON Col2 = EmployeeID
    Order by Col1
    GO
    
    DROP TABLE myTable99
    GO
    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.

  13. #13
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Brett's is the type of solution I'd prefer, but hopefully there is already some sort of logical data arrangment in your database so that you won't need to create a table just for this purpose. More explanation would be helpful.

Posting Permissions

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