locked
Microsoft SQL Server Management Studio query window INSERT statement results issue RRS feed

  • Question

  • Environment:
    Workstation: 16GB RAM. 500MB SSD
    Windows 10 Pro (64-bit)
    SQLServer 2019 (Express Edition) Just installed 2 weeks ago.

    Not available on workstation (though some supporting libraries may be present, e.g. .NET framework 3.5 && 4.8 for Windows 10):
    Visual Studio
    C# compiler
    etc.

    Task: Select entered VARCHAR data.

    Error/roadblock (steps to reproduce):

    Within Microsoft SQL Server Management Studio, 

    I have a simple database "test2020" with 1 table "eligibleperson" with 6 columns (2 int, 4 nvarchar), and 1 primary/identify key (personid).

    I hand-type (copied from an Excel spreadsheet) an INSERT statement:

    USE [test2020]
    GO
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO

    INSERT INTO dbo.eligibleperson
    Values
    ( 820682 , N' Michael ',N' K ',N' Alley ',N' Active ', 10003 ),
    ( 820686 , N' David ',N' J ',N' Anderson ',N' Active ', 10008 ),
    ( 820687 , N' Lynn ',N' C ',N' Anderson ',N' Active ', 10009 ),
    ( 820688 , N' Bob ',N' A ',N' Anderson ',N' Active ', 10010 ),
    ( 820689 , N' Will ',N' H ',N' Banker ',N' Active ', 10011 ),
    ( 820690 , N' Ranesse ',N' T ',N' Barsanti ',N' Active ', 10012 ),
    ( 820696 , N' Carol ',N' Reid ',N' Belew ',N' Active ', 10013 ),
    ( 820698 , N' Cathy ',N' L ',N' Bertucci ',N' Active ', 10015 ),
    ( 820699 , N' Dennis ',N' K ',N' Bertucci ',N' Active ', 10020 ),
    ( 820701 , N' Bruce ',N' W ',N' Bilgers ',N' Active ', 10030 ),
    ( 820709 , N' Irene ',N' M ',N' Boden ',N' Active ', 10036 ),
    ( 820717 , N' Carl ',N' E ',N' Brookes ',N' Active ', 10040 ),
    ( 820718 , N' Tom ',N' Scott ',N' Streamer ',N' Active ', 10050 )
    GO

    The records were successfully inserted:

    USE [test2020]
    GO
    select * from eligibleperson
    GO

    results:
    personid firstname middlename lastname countrystatus countryid

    820682   Michael K Alley                             Active            10003
    820686   David J Anderson                          Active            10008
    820687   Lynn C Anderson                           Active            10009
    820688   Bob A Anderson                            Active            10010
    820689   Will H Banker                                 Active            10011
    820690   Ranesse T Barsanti                         Active            10012
    820696   Carol Reid Belew                            Active            10013
    820698   Cathy L Bertucci                             Active            10015
    820699   Dennis K Bertucci                           Active            10020
    820701   Bruce W Bilgers                              Active            10030
    820709   Irene M Boden                               Active             10036
    820717   Carl E Brookes                                Active             10040
    820718   Tom Scott Streamer                       Active             10050

    Next I query:

    SELECT TOP (10) [personid]
          ,[firstname]
          ,[countrystatus]
      FROM [test2020].[dbo].[eligibleperson] where countrystatus='Active'
      GO

    No results are returned:  (0 rows affected)

    I also tried 

    SELECT TOP (10) [personid]
          ,[firstname]
          ,[countrystatus]
      FROM [test2020].[dbo].[eligibleperson] where countrystatus=' Active'
      GO

    No results are returned:  (0 rows affected)

    NOTE the leading whitespace in ' Active'



    I did notice this query

    SELECT TOP (6) [personid]

          ,[firstname]

          ,[countrystatus]

          ,LEN(personid) personidlength

          ,LEN(firstname) firstnamelength

          ,LEN(countrystatus) countrystatuslength

      FROM [test2020].[dbo].[eligibleperson]

      GO

    Returns unexpected results:

    personid firstname countrystatus personidlength firstnamelength countrystatuslength

    820682  Michael     Active             6                       9                         8

    820686  David        Active             6                       7                         8

    820687 Lynn          Active              6                       6                         8

    820688 Bob           Active              6                       5                         8

    820689 Will           Active              6                       6                          8

    820690 Ranesse    Active              6                       9                          8

    Notice

    personidlength is what I expect, 6

    firstnamelength is always longer than I would expect by 2

    countrystatuslength is always longer than I would expect by 2

    Is that a clue?

    TIA


    Wednesday, August 5, 2020 4:41 PM

All replies

  • You have leading and trailing spaces in all your strings before and after the single quote.  You should remove those.

    INSERT INTO dbo.eligibleperson
    Values
    ( 820682 , N'Michael',N'K',N'Alley',N'Active', 10003 ),
    ( 820686 , N'David',N'J',N'Anderson',N'Active', 10008 ),
    ( 820687 , N'Lynn',N'C',N'Anderson',N'Active', 10009 ),
    ( 820688 , N'Bob',N'A',N'Anderson',N'Active', 10010 ),
    ( 820689 , N'Will',N'H',N'Banker',N'Active', 10011 ),
    ( 820690 , N'Ranesse',N'T',N'Barsanti',N'Active', 10012 ),
    ( 820696 , N'Carol',N'Reid',N'Belew',N'Active', 10013 ),
    ( 820698 , N'Cathy',N'L',N'Bertucci',N'Active', 10015 ),
    ( 820699 , N'Dennis',N'K',N'Bertucci',N'Active', 10020 ),
    ( 820701 , N'Bruce',N'W',N'Bilgers',N'Active', 10030 ),
    ( 820709 , N'Irene',N'M',N'Boden',N'Active', 10036 ),
    ( 820717 , N'Carl',N'E',N'Brookes',N'Active', 10040 ),
    ( 820718 , N'Tom',N'Scott',N'Streamer',N'Active', 10050 )

    See:

    https://support.microsoft.com/en-us/help/316626/inf-how-sql-server-compares-strings-with-trailing-spaces


    Wednesday, August 5, 2020 4:51 PM
  • You should remove those.

    Totally agree.

    I am just confused as to why, given there is 1 leading space, I cannot successfully query/select on it as i did with

       where countrystatus=' Active'

    and the result was

    No results are returned:  (0 rows affected)

    Thursday, August 6, 2020 12:04 AM
  • Based on your test results, countrystatuslength is 8. So there are 2 leading spaces in the column countrystatus. Try WHERE countrystatus = '  Active'.

    A Fan of SSIS, SSRS and SSAS

    Thursday, August 6, 2020 1:13 AM