none
T-SQL equivalent RRS feed

  • Question

  • Hi

    I am using below LIKE expression in Access query. What is the T-SQL equivalent please? Also can I implement it on table level so the column always adheres to this validation?

    Thanks

    Regards

    SELECT AccountReference 
    FROM Clients
    WHERE (AccountReference Like "*[!0-9A-Z &,.@/'():+!-]*") Or (AccountReference Like "*[é]*")

    Monday, March 13, 2017 1:25 AM

All replies

  • For SQL, use wild character "%" in place of "*" of your above query.

    Regarding applying at table level, trying creating CHECK CONSTRAINT on the AccountReference column so that any new data entered /updated into this column would go through the required check.

    Thanks!

    • Proposed as answer by Albert_ Zhang Monday, March 20, 2017 3:36 PM
    Monday, March 13, 2017 4:26 AM
  • See this to understand how you can search for wildcard characters within a string

    http://visakhm.blogspot.in/2013/01/wildcard-character-based-pattern-search.html


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Monday, March 13, 2017 6:51 AM
  • Hi Y a h y a,

    You could use following T-SQL code to achieve the same goal with your above code that’s in Access.

    SELECT AccountReference 
    FROM Clients
    WHERE (AccountReference not Like '%[0-9A-Z &,.@/''():+!-]%') Or (AccountReference Like '%é%')
    

    As mentioned above, you could use Check Constraint with following code to let the column always adheres to this validation.

    create table test (val varchar(max))
    go
    alter table test add CONSTRAINT cnkval CHECK ((val not Like '%[0-9A-Z &,.@/''():+!-]%') Or (val Like '%é%'));
    go
    insert into test values ('éé');
    go
    insert into test values ('abcd');--happen error
    go
    select * from test 
    go
    drop table test
    go
    

    Best Regards,

    Albert Zhang


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Proposed as answer by Albert_ Zhang Monday, March 20, 2017 3:36 PM
    Monday, March 13, 2017 7:00 AM