Post reply

Select records start with %

  • July 28, 2015 at 9:39 am

    #299262

    Hi,

    I need to select records in column that start with '% ' -- %space e.g. column A = % mytest

    I tried select column name with like operator but it select records who start with % and ignore space

    select columnA from table where columna like '% %';

  • July 28, 2015 at 10:19 am

    #1815485

    thbaig (7/28/2015)

    Hi,

    I need to select records in column that start with '% ' -- %space e.g. column A = % mytest

    I tried select column name with like operator but it select records who start with % and ignore space

    select columnA from table where columna like '% %';

    Quick suggestion, use LIKE with regexp, as a bonus it will do an index seek if the correct index is in place.

    😎

    USE tempdb;

    GO

    SET NOCOUNT ON;

    GO

    IF OBJECT_ID('dbo.TBL_TEST_RX') IS NOT NULL DROP TABLE dbo.TBL_TEST_RX;

    CREATE TABLE dbo.TBL_TEST_RX

    (

    TTR_ID INT IDENTITY(1,1) NOT NULL CONSTRAINT PK_dbo_TBL_TEST_RX_TTR_ID PRIMARY KEY CLUSTERED

    ,TTR_TXT VARCHAR(50) NOT NULL

    );

    GO

    CREATE NONCLUSTERED INDEX NCLIDX_DBO_TBL_TEST_RX_TTR_TXT ON dbo.TBL_TEST_RX(TTR_TXT ASC) INCLUDE (TTR_ID);

    GO

    INSERT INTO dbo.TBL_TEST_RX (TTR_TXT)

    VALUES ('TEXT 1')

    ,('% TEXT 2')

    ,('TEXT 3')

    ,('%TEXT 4')

    ,('TEXT 5')

    ,('% TEXT 6');

    SELECT

    S.TTR_ID

    ,S.TTR_TXT

    FROM dbo.TBL_TEST_RX S

    WHERE S.TTR_TXT LIKE '[%][ ]%';

    Results

    TTR_ID TTR_TXT

    ----------- -----------

    2 % TEXT 2

    6 % TEXT 6

    Edit: improved sample code.

  • July 28, 2015 at 10:25 am

    #1815488

    thank you Eirikur Eiriksson

  • July 28, 2015 at 10:29 am

    #1815489

    thbaig (7/28/2015)

    thank you Eirikur Eiriksson

    You are very welcome!

    😎

    I just improved the code sample to demonstrate that query will use index seek if the proper index is in place.

  • July 28, 2015 at 11:18 am

    #1815506

    thbaig (7/28/2015)

    thank you Eirikur Eiriksson

    Now that you know how it can be done, you should take the opportunity lookup "LIKE" in "Books Online" (the help system for SQL Server) and read more about wildcard-lookups, etc, etc.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • July 29, 2015 at 8:10 am

    #1815738

    sure jeff , i will

  • July 29, 2015 at 9:15 am

    #1815767

    Alternately

    LIKE '/% %' ESCAPE '/'

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • July 30, 2015 at 7:47 am

    #1816123

    For this example, why even use LIKE?

    SELECT

    S.TTR_ID

    ,S.TTR_TXT

    FROM dbo.TBL_TEST_RX S

    --WHERE S.TTR_TXT LIKE '[%][ ]%';

    WHERE LEFT(S.TTR_TXT, 2) = '% ';

  • July 30, 2015 at 7:51 am

    #1816126

    Brian Barkauskas (7/30/2015)

    For this example, why even use LIKE?

    Because LIKE with a trailing wildcard allows for index seeks if there is an appropriate index, whereas LEFT does not.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • July 30, 2015 at 7:58 am

    #1816135

    Yes, got it, sorry.

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply