Problem with the Query

Question

Post reply

Problem with the Query

Viewing 5 posts - 16 through 19 (of 19 total)

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

RockingAdmin SSCrazy Points: 2828 More actions · Answer 1

Hi nageswara.dhaveji,

this works fine.

thank you very much for all your support on this issue.

thanks and regards

ROCK...

Saurabh Dwivedy SSCommitted Points: 1887 More actions · Answer 2

DECLARE @t table (name varchar(100), fname varchar(50), lname varchar(50))

INSERT INTO @t

SELECT 'Bill+Gates', '','' UNION ALL

SELECT '+Steve', '','' UNION ALL

SELECT 'Jobs+', '', ''

UPDATE @t

SET fname = SUBSTRING(name,1,CASE WHEN CHARINDEX('+', name) > 1 THEN CHARINDEX('+', name)-1 ELSE LEN(name) END),

lname = SUBSTRING(name, CASE WHEN CHARINDEX('+', name) > 1 THEN CHARINDEX('+', name)+1 ELSE LEN(name)+1 END,

CASE WHEN CHARINDEX('+', name) > 1 THEN LEN(name) ELSE 0 END)

SELECT * FROM @t

GO

Dear RockingAdmin,

Please see if this works. If I understood your requirement correctly, in the case where the name begins with a +sign, you want it to appear in FirstName only; so for example in cases where

name = +Jobs

You want FNAME to have +Jobs.

Let me know if my understanding about your requirement is correct. I think the other solutions are slightly different from what I am proposing here.

Regards,

Saurabh.

Saurabh Dwivedy
___________________________________________________________

My Blog: http://tinyurl.com/dwivedys

For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537

Be Happy!

Saurabh Dwivedy SSCommitted Points: 1887 More actions · Answer 3

nageswara.dhaveji (5/7/2009)
Hi,
I have modified my query which I have posted earilier... try this ...
create table #temp (name VARCHAR(100), first_name VARCHAR(50), last_name VARCHAR(50))
truncate table #temp
INSERT INTO #temp (name)
SELECT 'FIRST+LAST'
UNION ALL SELECT '+LAST'
UNION ALL SELECT 'FIRST+'
UNION ALL SELECT 'FIRST name'
UPDATE #temp SET first_name = SUBSTRING(name, 1, case isnull(patINDEX('%+%', name),0) when 0 then len(name) else
patINDEX('%+%', name)-1 end),
last_name = case isnull(patindex('%+%', name),0) when 0 then ' ' else SUBSTRING(name, PATINDEX('%+%', name)+ 1, LEN(name)) end
SELECT * FROM #temp

Pretty Nice Nageswara!

Saurabh Dwivedy
___________________________________________________________

My Blog: http://tinyurl.com/dwivedys

For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537

Be Happy!

RockingAdmin SSCrazy Points: 2828 More actions · Answer 4

hi saurabh.dwivedy

this works perfect to my requrement

thanks you very much

rock..

dhulme Mr or Mrs. 500 Points: 570 More actions · Answer 5

Your code fails when there is no '+' in the string, causing the right() function to fail. You could simply update ... etc WHERE the old_column does contain a '+'.

partial update

Update table

set new_column = [function of old_column]

where [old_column meets condition]

Alternatively, you can handle each possible scenario, or "case", by using the CASE statement: -

full table update

Update table

SET new_column = case when old_column LIKE'%_+_%' THEN --- has at least two strings separated by '+'

when old_column LIKE'+_%' THEN --- has at least one string and starts with '+'

when old_column LIKE'%_+' THEN --- has at least one strings and ends with '+'

else NULL

END

I wouldn't recommend using LIKE unless you have a tiny table, lots of CPU spare, and/or only need to do this once. There are many string-splitting examples on this site (look for "tally table") that you can tweak to get forename(s) and surname out of "string+string","+string", and "string+".