Access SQL -> Transact SQL String Functions 
Author Message
 Access SQL -> Transact SQL String Functions

Look at patindex for IIF, substring for MID, and datalength for LEN
Also check out the CASE statement. I thin kyuou may find it useful if
you're doing T-SQL like that...

--
Brian Moran
Chief Architect
Spectrum Techology Group
SQLServer MVP, MCSE, MCSD, MCT

Check out my monthly SQL column
in Windows NT Magazine!



Quote:
> Does anyone have any idea how I can reproduce this IIf... Mid..., Len
> function from an Access database in to valid Transact SQL?
> SELECT DISTINCTROW[Enquiry/PO].EnqNo,
> IIf(InStr([EnqNo],"PO"),"",Mid([EnqNo],7,Len([EnqNo])-7)) AS cReqNoEnq
> FROM [Enquiry/PO];

> Any help would be much appreciated.
> Julia



Sat, 04 Mar 2000 03:00:00 GMT
 Access SQL -> Transact SQL String Functions

Does anyone have any idea how I can reproduce this IIf... Mid..., Len
function from an Access database in to valid Transact SQL?
SELECT DISTINCTROW[Enquiry/PO].EnqNo,
IIf(InStr([EnqNo],"PO"),"",Mid([EnqNo],7,Len([EnqNo])-7)) AS cReqNoEnq
FROM [Enquiry/PO];

Any help would be much appreciated.
Julia



Sat, 04 Mar 2000 03:00:00 GMT
 Access SQL -> Transact SQL String Functions

Try replacing IIf with CASE, InStr with CHARINDEX, Mid with SUBSTRING, and
Len with DATALENGTH.  Here is an example to run against the sample Pubs
database in SQL Server:

select
        name_string =
        case
          when charindex('g', au_lname) > 0 then ''
          else substring(au_lname, 3, datalength(au_lname) - 45)
        end
from
        authors
--
Bob Pfeiff, MCSD
Spectrum Technology Group, Inc.

<<remove "nospam" from address before responding via email>>



Sat, 04 Mar 2000 03:00:00 GMT
 Access SQL -> Transact SQL String Functions



Quote:
> Does anyone have any idea how I can reproduce this IIf... Mid..., Len
> function from an Access database in to valid Transact SQL?
> SELECT DISTINCTROW[Enquiry/PO].EnqNo,
> IIf(InStr([EnqNo],"PO"),"",Mid([EnqNo],7,Len([EnqNo])-7)) AS cReqNoEnq
> FROM [Enquiry/PO];

> Any help would be much appreciated.
> Julia

I believe this would be your T-SQL:

SELECT  EnqNo,
                CASE CHARINDEX('PO', EnqNo)
                        WHEN 0 THEN SUBSTRING(EnqNo, 7, DATALENGTH(EnqNo) - 7)
                        ELSE ''    --Two single quotes
                END AS cReqNoEnq
FROM            Enquiry_PO

-Peter

--
Peter W. DeBetta

http://www.milori.com

"Two roads diverged in a wood, and I-
I took the one less traveled by,
And that has made all the difference."



Fri, 10 Mar 2000 03:00:00 GMT
 
 [ 4 post ] 

 Relevant Pages 

1. Transact SQL Aggregate Function for String

2. String Functions in Transact SQL

3. Manipulating Strings Using Transact-SQL (Spliting Strings)

4. Access Query => Stored Procedure Transact SQL Query

5. HELP Transact-SQL -> PL/SQL porting

6. Transact SQL -> SQL

7. Help Transact-SQL -->PL/SQL porting

8. cross-compiler PL/SQL -> Transact-SQL

9. cross-compiler PL/SQL -> Transact-SQL

10. Access SQL to Transact SQL

11. Old Article: Access SQL syntax versus Transact SQL syntax

12. How to evaluate a string expression in Transact SQL


 
Powered by phpBB® Forum Software