Results 1 to 5 of 5
  1. #1
    4 Star Lounger
    Join Date
    Feb 2001
    Location
    Gillingham, Kent, England
    Posts
    511
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Access to SQL (SQL Server 2000)

    I have been using Access for a few years now and slowly starting to do more and more in SQL, however, some of the queries I used in Access do not have the same command in SQL. Would anyone know of a good place to look these up? I've tried the books online link you get when installing SQL and i've waded though a bit of MSDN with varying degrees of success.

    Specifically, i'm currently searching for the SQL command for the Access command strcomp.

    I have the following:
    SELECT StrComp([postcode],Ucase([postcode]),0) AS PcodeUpper, tbleducation.Postcode
    FROM tbleducation
    WHERE (((StrComp([postcode],Ucase([postcode]),0))>0));

    In SQL I think Ucase needs to be replaced by UPPER, but I cannot find the SQL equivalent of StrComp.

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Access to SQL (SQL Server 2000)

    As far as I know there is not SQL equivalent to StrComp. You'll have to learn to build your own UDFs in SQL Server to handle things like that. There are several books to ease the pain of migration, including "Access to SQL Server", by Russell Sinclair. For reference, I like O'Reilly's "SQL in a Nutshell" and "Transact-SQL Programming", both of which are quite succinct, as with all the books from O'Reilly.
    Charlotte

  3. #3
    4 Star Lounger
    Join Date
    Feb 2001
    Location
    Gillingham, Kent, England
    Posts
    511
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access to SQL (SQL Server 2000)

    Thanks Charlotte. Looks like ill have to get reading then. Had to happen sooner or later where SQL was concerned!

  4. #4
    4 Star Lounger
    Join Date
    Feb 2001
    Location
    Gillingham, Kent, England
    Posts
    511
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access to SQL (SQL Server 2000)

    Thanks for your suggestion Charlotte, I managed to work it out using online books and that brain of mine!

    I created a user defined function called strComp and called it using:
    select tbleducationlive.postcode, dbo.strcomp(tbleducationlive.postcode) as result
    from tbleducationlive where educationnumber = 1

    The function is:
    CREATE FUNCTION strComp(@postcode as varchar(10))
    RETURNS INT AS
    BEGIN
    DECLARE @Result AS INT, @i as int, @PcodeLen as int

    Set @PcodeLen = len(@Postcode)
    SET @Result = 0
    set @i = 1
    while @i <= @PcodeLen
    BEGIN
    if (ASCII(SUBSTRING(@postcode,@i,1)) not like ASCII(SUBSTRING(Upper(@postcode),@i,1)))
    Begin
    set @Result = 1
    End
    set @i = sum(@i+1)
    END

    RETURN(@Result)
    END

  5. #5
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Access to SQL (SQL Server 2000)

    In addition to the books Charlotte suggested, you might consider "Advanced Transact-SQL for SQL Server 2000" by Ben-Gan and Moreau, "Microsoft SQL Server 2000 DBA Survival Guide" by Spenik and Sledge, and "Professional SQL Server 2000 Programming" by Vieira and Maston. The last, a WROX book, deals specifically with user defined functions.
    Wendell

Posting Permissions

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