MS SQL equivalent of below MYSql code

Databases
1

Hi All,

can anyone provide me the MS SQL equivalent or the meaning of below MYSQL code.

select sum(distinct floor(pow(2,field(Column,8,13,15,53,55,6,73,75,3,42,41,45,44)-1))) as FinalColumn from Table

Thanks for the help in advance…

2

Not 100% sure (I’m a ms sql guy myself and I know enough mySQL to be dangerous).

You could give this method a try. Combine that with Rudy’s post above and it might give you what you need…

3

Thanks for all your help Dave…

4

i’d be happy to, this kind of question really tickles me

okay, when working on sql, one tip is always start thinking at the innermost point (this is especially useful for subqueries)

so in this expression, the innermost part is


FIELD(Column,8,13,15,53,55,6,73,75,3,42,41,45,44)

the mysql FIELD function compares the value of the first parameter (in this case a column artfully named “Column”) and tries to find it in the list of values which follow, returning the ordinal index if found, or zero

the sqlserver equivalent might be


CASE Column WHEN 8 THEN 1
            WHEN 13 THEN 2
        ... WHEN 44 THEN 13 ELSE 0 END

if we call this number “lucky” then working our way outwards, next we have


POW(2,unlucky-1)

which returns the number 2 raised to the power of -1 through 12, so ½, 1, 2, 4, 8, … 4096

the sqlserver equivalent would be


POWER(2,unlucky-1)

then we take the FLOOR of that, which only has an effect on the ½, changing it to 0, and the FLOOR function is identical in both databases

at this point we’re inside a SUM(DISTINCT … ) which also works the same in both databases

so there you have it

:slight_smile:

5

OK, the quick answer is this will give you the same result:


declare @var_str varchar(150)
declare @x int
 
set @x = 1
set @var_str = 'http://www.sitepoint.com/forums/showthread.php?p=4690916#post4690916'
 
SELECT CASE CHARINDEX('/', @var_str)
                   WHEN 0 THEN @var_str
                   ELSE SUBSTRING(@var_str, 1, CharIndex('/', @var_str) - 1)
           END

More detailed answer:

What the substring_index function in mySQL does is return the text from a source value from an end of the text up to the nth occurrence of the text. If the number is positive, it starts from the front of the text and if it’s a negative, it takes if from the end of the text.

So if we used the url of this page as the text being searched from and your method, it will pull all the text from the front of the URL up to (but not including) the first / (we’ll call it TextOne. It will then parse the TextOne value and return all of the text from the end to the first / it encounters - since there’s none it returns the whole value.

In MS SQL, there’s no SUBSTRING_INDEX equivalent, but you can achieve the same effect by using SUBSTRING and CHARINDEX. Now if you wanted to pull the right most text up to the last /, you would use a slightly different query:


declare @var_str varchar(150)
declare @x int
 
set @x = 1
set @var_str = 'http://www.sitepoint.com/forums/showthread.php?p=4690916#post4690916'
 
SELECT CASE CHARINDEX('/', reverse(@var_str))
                   WHEN 0 THEN @var_str
                   ELSE RIGHT(@var_str, CharIndex('/', reverse(@var_str)) - 1)
           END
6

Thanks for your help… I have one more question for you if you can help…

Can you please help me with the MS SQL code for below MYSQL function?

declare @x int
set @x = 1

substring_index(substring_index(var_str,“/”,@x),“/”,-1)

Thanks in advance…

7

Okay great… I’ll just use

declare @var_str varchar(150)declare @x int
SET @x = 1SET @var_str = ‘http://www.sitepoint.com/forums/showthread.php?p=4690916#post4690916’
SELECT CASE CHARINDEX(‘/’, @var_str) WHEN 0 THEN @var_str ELSE SUBSTRING(@var_str, 1, CharIndex(‘/’, @var_str) - 1) END

Thanks much for your help Dave…And here’s one more question for you (probably last)…

select group_concat(col1 order by field(Column,8,13,15,53,55,6,73,75,3,42,41,45,44)) as col1

what will be MS SQL for this? I am sorry but I am a MSSQL guy and stuck with debugging lot of MySQL thesedays.

8

If you mean SELECT LEFT(var_str, CHARINDEX(‘/’, var_str) - 1), then yes it’s the same.

9

Thanks for the informative reply. I think I need to use just the first query you provided.

I do have one question though,

isnt that the same as doing “select LEFT (var_str, 1)”