[b]Problems Extracting Names using Substring and Charindex [/b] 1

[Nassy]

Hi,

What I am trying to do

I have a table ApplicantDetails that contains a field Salutation that stores names in the following formats:

Salutation
----------

Mr Smith
Mr Bevan and Mrs Bevan
Unknown
Mr Jeeves and Mrs Maplin
Mr Dunne and Mrs Dunne
Mr Creek and Ms Jenkins

The idea is to use the output from this field to create letters. The problem is that the salutation is sometimes wrong ie Mr Bevan and Mrs Bevan and Mr Dunne and Mrs Dunne should be stored as Mr and Mrs Bevan and Mr and Mrs Dunne respectively. I have therefore been trying to write code to update these names accordingly

How I am trying to do this (and failing)

I have been trying to write some code that uses the Substring and the Charindex function to clean up some of the names. A common pattern is Mr ? and Mrs ? so I am trying to grab the surname that follows the 'Mr ' and the surname that follows the 'Mrs ' to see if they are the same. If they are then I can easily create a new Salutation based on the surname.

To start with I have written a short bit of code that is designed to extract the surname that follows the 'Mr ' keyword:


Select Substring(Salutation,charindex('Mr ',Salutation),charindex('and ',Salutation)-1 - charindex('Mr ',Salutation)+2) from ApplicantDetails


The problem

I have played around with the code above but everytime I get an invalid length parameter error so I am not getting anywhere with the surname comparison! I have tried using the NULLIF function and removing and names that don't have 'Mr' in them beforehand but I still get the error.

Any ideas would be welcome as I am literally pulling my hair out. If only the Substring function could take character position instead of number of chars as a third parameter life would be so much easier ;-) !

Thanks

Nassy

 

[clapag22]

declare @v varchar(50),
	@pv varchar(50),
	@name1 varchar(20),
	@name2 varchar(20)

select @v = 'Mr Dunne and Mrs Dunne'

if @v like 'mr%and mrs%'
	begin

	select @pv = replace(@v, 'mr ', '')

	select @name1 = left(@pv, patindex('% %', @pv) - 1)

	select @name2 = reverse(left(reverse(@pv), patindex('% %', reverse(@pv)) - 1))

	if @name1 = @name2
		begin
		select @v = 'Mr and Mrs ' + @name1
		end

	end

select @v

Craig

 

[SQLBill]

I would 'grab' the space after Mr also. Why? Well Mrs will be 'caught' if you are just looking for Mr without the space.

-SQLBill

Posting advice: FAQ481-4875

 

[clapag22]

Good point. Nice catch.

Craig

 

[Nassy]

Thanks everyone,

I'll give this code a go.

Nassy