More than one character

Question

More than one character

TSQL Tryer

SSCrazy Eights

Points: 8869
More actions
November 12, 2013 at 9:14 am

#301089

Hi there,
In SQL2000 I used to use the following in my SELECT statement -
andlen ([Initials]) > 1
However it doesn't appear to work anymore in 2012.
Basically it will look at the Initials field and pull back anything that has more than one character. So only one character should ever exist and I want to know where there are more than one. Is LEN the correct command to use?
Thanks

Viewing 4 posts - 1 through 3 (of 3 total)

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

Keith Tate SSCrazy Eights Points: 9938 More actions · Answer 1

So you want to find any records where there is more than 1 character in the initials column? And you are saying this doesn't work for you?

where len(initiial) > 1

What are the results you are getting? Are you getting back results that only have a single character? If so, make sure there are no trailing spaces in the column. You can use LTRIM and RTRIM for normal spaces.

Microsoft Certified Master - SQL Server 2008
Follow me on twitter: @keith_tate

Forum Etiquette: How to post data/code on a forum to get the best help[/url]

TSQL Tryer SSCrazy Eights Points: 8869 More actions · Answer 2

That's correct.

When I run the command - I get no results in SQL2012. But in 2000 I know there are [INITIALS] that have more than one character - example below -

PERSON-REF PERSON-TITLE INITIALS FORENAMES SURNAME corr-name1 corr-name2 D-O-B NHI-NO ORIGIN-CODE GENDER tenancy-ref tncy-start ON-TNCY

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

11281432180006 Mr V H Valerio Honti Arduini Mr V M Arduini Mr Arduini 1952-10-19 00:00:00.000 NA681962C W1 M 3018470190036 2013-11-04 00:00:00.000 yes

12253328330013 Mr R W Richard Wymer Bowles Mr & Mrs RW Bowles Mr & Mrs Bowles 1956-05-15 00:00:00.000 YY264426C R M 2010751950002 2013-10-18 00:00:00.000 yes

12253330200025 Mrs T J Tracey Jane Bowles Mr & Mrs RW Bowles Mr & Mrs Bowles NULL NULL r F 2010751950002 2013-10-18 00:00:00.000 yes

17251433170001 Mr WG William George Godfrey Mr W G Godfrey Mr Godfrey 1952-12-04 00:00:00.000 NULL W1 M 3118640120022 2013-11-04 00:00:00.000 yes

22313015110005 Mr E A Edwin Anthony Lutton Mr EA Lutton Mr Lutton NULL NULL r M 2114410420004 2013-10-04 00:00:00.000 yes

26252812200004 Miss B J Billie-Jo Porter Mrs K Porter & Mr R Porter Mrs Porter & Mr Porter 2000-01-26 00:00:00.000 NULL W1 F 2011101470007 2013-10-14 00:00:00.000 no

29112223110006 Mr M A Matthew Alexander Salisbury Mr MA Salisbury & Mr MA Salisbury & Ms R Frith Mr Salisbury & Mr Salisbury & Ms Frith NULL NULL r M 2114491220002 2013-09-12 00:00:00.000 yes

29112223110011 Mr M A Mark Andrew Salisbury Mr MA Salisbury & Mr MA Salisbury & Ms R Frith Mr Salisbury & Mr Salisbury & Ms Frith NULL NULL r M 2114491220002 2013-09-12 00:00:00.000 yes

33192218220000 Miss HL Heaven-Lee Wilde Miss HL Wilde Miss Wilde 1997-04-26 00:00:00.000 PC202392A W1 F 2553500260129 2013-10-21 00:00:00.000 yes

(9 row(s) affected)

So in SQL2000 these are the example I have above. The data is the same in 2000 and 2012.

TSQL Tryer SSCrazy Eights Points: 8869 More actions · Answer 3

Hi,

It's OK. i've worked it out there is something very weird going on with some code I have inherited. When I strip over stuff out of the WHERE it works.

I will have to investigate the other parts to it. Please ignore this topic.