Substring with conditional logic

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Substring with conditional logic

Author

Topic

qman
Constraint Violating Yak Guru

442 Posts

Posted - 2007-12-31 : 08:00:26

I was hoping someone could help me with the use of substring and conditional logic.

Details:
My first_name column consists of values including first name (John) and some with first name with middle initial (John A.). Some could also consist of a two name first name followed by an initial (Jamie Lynn A.).

I have already created a column called middle_initial. I would like to strip the values out of the first_name column that include a middle_initial and store these values in the newly created middle_name column.

I assume substring would handle this. I thought this could be done by finding out the overall number of character, then starting at the last character, substring to the first space found (John A.). This would isolate the middle initial. Also, how would one handle a case were two spaces are found such as a two name first name followed by middle initial (Jamie Lynn A.).

Thanks, happy New Year!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2007-12-31 : 08:22:35

Are you telling that you have middle_initial value already stored on a field and need only strip off the value from first_name column except middle initial and put remaining onto middle_name column?

if yes, try this:-


UPDATE Table
SET middle_name=LEFT(first_name,CHARINDEX(middle_initial,first_name)-1)
WHERE CHARINDEX(middle_initial,first_name)>0

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-12-31 : 08:42:58

And first try SELECT
If this returns what you want, then update

SELECT LEFT(first_name,CHARINDEX(middle_initial,first_name)-1) as middle_name
FROM yourTable
WHERE CHARINDEX(middle_initial,first_name)>0

Madhivanan

Failing to plan is Planning to fail

qman
Constraint Violating Yak Guru

442 Posts

Posted - 2007-12-31 : 08:56:23

Thanks for your help, this query is not quite correct.

I have a first_name column consiting of data which includes first name (Mike) and first name with middle initial (Mike A.). Where applicable, I want to isolate the middle initial values and set these values in the middle initial column. I then want to remove these values from the first_name column.

I need to also include some logic that will handle names with two first names such as (Jamie Lynn A.)

I appreciate your help, thanks again!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2007-12-31 : 09:19:19

Try this:-

UPDATE Table
SET middle_initial=
CASE
WHEN SUBSTRING(LTRIM(RTRIM(first_name)),LEN(LTRIM(RTRIM(first_name)))-2,1)=' '
THEN SUBSTRING(LTRIM(RTRIM(first_name)),LEN(LTRIM(RTRIM(first_name)))-1,1)
ELSE NULL
END,
first_name=
CASE
WHEN SUBSTRING(LTRIM(RTRIM(first_name)),LEN(LTRIM(RTRIM(first_name)))-2,1)=' '
THEN LEFT(LTRIM(RTRIM(first_name)),LEN(LTRIM(RTRIM(first_name)))-3)
ELSE LTRIM(RTRIM(first_name))
END

qman
Constraint Violating Yak Guru

442 Posts

Posted - 2007-12-31 : 09:33:48

Nice visakh16,

Think this will do it, would you mind supplying me the same logic in a select statement. I would like to see the results prior to doing the update. Hope this is not too much trouble.

Thanks again!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2007-12-31 : 09:40:43

[code]SELECT
CASE
WHEN SUBSTRING(LTRIM(RTRIM(first_name)),LEN(LTRIM(RTRIM(first_name)))-2,1)=' '
THEN SUBSTRING(LTRIM(RTRIM(first_name)),LEN(LTRIM(RTRIM(first_name)))-1,1)
ELSE NULL
END AS 'middle_initial',
CASE
WHEN SUBSTRING(LTRIM(RTRIM(first_name)),LEN(LTRIM(RTRIM(first_name)))-2,1)=' '
THEN LEFT(LTRIM(RTRIM(first_name)),LEN(LTRIM(RTRIM(first_name)))-3)
ELSE LTRIM(RTRIM(first_name))
END AS 'first_name_removing_initials'
FROM Table[/code]

qman
Constraint Violating Yak Guru

442 Posts

Posted - 2007-12-31 : 09:58:33

When the select is run, it produces the following:

Invalid length parameter passed to the SUBSTRING function.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2007-12-31 : 10:16:17

Sorry i cant find out any errors in SELECT (I'm at home and dont have a SQL box to run & test). Can you run below and see if it returns anything

SELECT first_name
FROM Table
WHERE LEN(first_name) <=2

qman
Constraint Violating Yak Guru

442 Posts

Posted - 2007-12-31 : 10:17:32

Three records are returned....

Jo
Al
Al

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2007-12-31 : 10:27:22

Try this then:-
SELECT
CASE
WHEN SUBSTRING(LTRIM(RTRIM(first_name)),LEN(LTRIM(RTRIM(first_name)))-2,1)=' '
THEN SUBSTRING(LTRIM(RTRIM(first_name)),LEN(LTRIM(RTRIM(first_name)))-1,1)
ELSE NULL
END AS 'middle_initial',
CASE
WHEN SUBSTRING(LTRIM(RTRIM(first_name)),LEN(LTRIM(RTRIM(first_name)))-2,1)=' '
THEN LEFT(LTRIM(RTRIM(first_name)),CASE WHEN LEN(LTRIM(RTRIM(first_name)))>=3 THEN LEN(LTRIM(RTRIM(first_name)))-3 ELSE LEN(LTRIM(RTRIM(first_name))) END)
ELSE LTRIM(RTRIM(first_name))
END AS 'first_name_removing_initials'
FROM Table

qman
Constraint Violating Yak Guru

442 Posts

Posted - 2007-12-31 : 10:32:56

visakh16,

Thanks for your help... I think I have enough here to keep me busy.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2007-12-31 : 10:38:56

You are welcome. Try this out and let us know if you face any more problem.

qman
Constraint Violating Yak Guru

442 Posts

Posted - 2007-12-31 : 12:57:40

Hi visakh16

After playing a round a bit, here is what I came up with.....

SELECT count(*), firstpres,
CASE
WHEN count(charindex(' ',firstpres)+1) >= 1
THEN SUBSTRING(firstpres, charindex(' ',firstpres)+1, len(firstpres))
ELSE NULL
END AS 'middle_initial'
FROM dmcmain_import
where firstpres is not null and firstpres like '% %'
group by firstpres

When run, a sample of the results looks like

count, first_name, middle_initial
1, Alexander H, H
2, Allan I, I
1, Allan W, W
1, Andrew D, D
1,C Brian, Brian
1,Carol A, A
1, Caroline S, S
1, D Duane, Duane
2, D Scott, Scott

I still need some assistance modifying this query to not inclue names like C Brian, D Duane, D Scott, etc....

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2007-12-31 : 13:30:21

Did you tried my modified query?

i.e

Try this then:-
SELECT
CASE
WHEN SUBSTRING(LTRIM(RTRIM(first_name)),LEN(LTRIM(RTRIM(first_name)))-2,1)=' '
THEN SUBSTRING(LTRIM(RTRIM(first_name)),LEN(LTRIM(RTRIM(first_name)))-1,1)
ELSE NULL
END AS 'middle_initial',
CASE
WHEN SUBSTRING(LTRIM(RTRIM(first_name)),LEN(LTRIM(RTRIM(first_name)))-2,1)=' '
THEN LEFT(LTRIM(RTRIM(first_name)),CASE WHEN LEN(LTRIM(RTRIM(first_name)))>=3 THEN LEN(LTRIM(RTRIM(first_name)))-3 ELSE LEN(LTRIM(RTRIM(first_name))) END)
ELSE LTRIM(RTRIM(first_name))
END AS 'first_name_removing_initials'
FROM Table

qman
Constraint Violating Yak Guru

442 Posts

Posted - 2007-12-31 : 13:45:51

Unfortunately after more analysis, it does not return what I am looking for.

See results:
middle_initial, first_name_removing_initials
NULL, D Wesley
NULL, Michael J
NULL, William E
NULL, Phillip
NULL, Tony
NULL, Kern
NULL, NULL
NULL, W Bradley
NULL, Michael B
NULL, David
NULL, W Hal
NULL, David

When reviewing results produced from your query, in cases like "D Wesley", the name should be ignored and remain in the first_name column because the middle name is more than one character. In cases like Michael J, the J should be stripped from the first_name column and added to the middle_initial column. In cases like Phillip, the name should be ignored and remain in the first_name column because of no middle initial.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2007-12-31 : 13:56:57

I think i got problem. I had thought name always ends with '.' as per your original post and had accounted for it too. Now i need to make a slight modification:-

SELECT
CASE
WHEN SUBSTRING(LTRIM(RTRIM(first_name)),LEN(LTRIM(RTRIM(first_name)))-1,1)=' '
THEN SUBSTRING(LTRIM(RTRIM(first_name)),LEN(LTRIM(RTRIM(first_name))),1)
ELSE NULL
END AS 'middle_initial',
CASE
WHEN SUBSTRING(LTRIM(RTRIM(first_name)),LEN(LTRIM(RTRIM(first_name)))-1,1)=' '
THEN LEFT(LTRIM(RTRIM(first_name)),CASE WHEN LEN(LTRIM(RTRIM(first_name)))>=2 THEN LEN(LTRIM(RTRIM(first_name)))-2 ELSE LEN(LTRIM(RTRIM(first_name))) END) 
ELSE LTRIM(RTRIM(first_name))
END AS 'first_name_removing_initials'
FROM Table

try this out and see if it gives you the result.

qman
Constraint Violating Yak Guru

442 Posts

Posted - 2007-12-31 : 14:03:45

You rule, that did it!

If you were closer I would send you a six pack.

Thanks for your help

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2007-12-31 : 14:05:42

Cool man. Cheers. Have a great rocking 2008!

qman
Constraint Violating Yak Guru

442 Posts

Posted - 2007-12-31 : 14:15:29

Same to you...

dineshrajan_it
Posting Yak Master

217 Posts

Posted - 2008-01-29 : 06:03:56

DECLARE @TAB TABLE
(
FIRSTNAME VARCHAR(50)
)

INSERT @TAB
SELECT 'DINESH'
UNION ALL
SELECT 'ARUN P.'
UNION ALL
SELECT 'SENTHIL KUMAR S.'
SELECT * FROM @TAB

SELECT FIRSTNAME =
CASE WHEN CHARINDEX(' ',FIRSTNAME) = '0' THEN FIRSTNAME
ELSE LEFT(FIRSTNAME,CHARINDEX(' ',FIRSTNAME)) END,
MIDDLENAME =
CASE WHEN CHARINDEX(' ',FIRSTNAME) = '0' THEN ''
ELSE
SUBSTRING(FIRSTNAME,LEN(LEFT(FIRSTNAME,CHARINDEX(' ',FIRSTNAME)))+ 1,LEN(FIRSTNAME))
END
FROM @TAB

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources