'' = ' ' ???

• Martin Bastable

  SSCommitted

  Points: 1766

  More actions

  November 7, 2007 at 4:56 am

  #200239

  Hi all

  Im checking for the existance of a space in a string, which may or may not have enough characters in it to fill to the position being checked.

  i.e.

  IF (SubString(@post_code, 5, 1) = ' ')....

  However, on post_code values smaller than 5 letters, I was still getting matches.

  So I investigated. SubString seems to return '' if its looking into `empty space`.

  Finally got down to this test....

  IF ('' = ' ') PRINT 'Match' ELSE PRINT 'No Match'

  this prints `Match`

  DECLARE @space nchar(1)

  SET @space = ' '

  IF ('' = @space) PRINT 'Match' ELSE PRINT 'No Match'

  this prints `Match`

  any other comparisons (e.g. '' = 'A') return no match

  So - does '' = ' ' ??????????

  (Or is there another way I can check for the existance of a space at a certain position in a string, without turning things into a hack.)

  Heeeeelp!!!!!

  Many thanks

  martin 🙂

• Johannes Fourie

  Default port

  Points: 1470

  More actions

  November 7, 2007 at 5:04 am

  #749041

  Try using the ASCII function, a blank string will return NULL otherwise it will return the ASCII code

  This following statement returns No Match:

  IF ISNULL(ASCII(''), 0) = ASCII(' ')

  PRINT 'Match'

  ELSE

  PRINT 'No Match'

• MarkusB

  SSC-Dedicated

  Points: 37370

  More actions

  November 7, 2007 at 5:23 am

  #749049

  Martin,

  you could try using the CHARINDEX function.

  IF CHARINDEX (' ', postcode) = 5 ...

  Markus

  [font="Verdana"]Markus Bohse[/font]

• Carl Federl

  One Orange Chip

  Points: 25384

  More actions

  November 7, 2007 at 5:31 am

  #749050

  Per the SQL Standard, trailing spaces are ignored in comparision.

  For substring, if the starting position is greater than the actual data length, then a zero length varchar is returned.

  Based on these two algorithms, a comparision of a zero length varchar to a single space returns true.

  Try using the a pattern match function - patindex. Here is example for the Canadian postal code:

  declare @postalcodechar(7)

  set@postalcode = 'M4B 1V4'

  selectCASE patindex('[A-Z][0-9][A-Z] [0-9][A-Z][0-9]', @postalcode)

  when 1 then 'Valid' else 'Invalid' end , @postalcode

  set@postalcode = '999999'

  selectCASE patindex('[A-Z][0-9][A-Z] [0-9][A-Z][0-9]', @postalcode)

  when 1 then 'Valid' else 'Invalid' end , @postalcode

  SQL = Scarcely Qualifies as a Language