separating data in a field - SQL Server Forums

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 2000 Forums

Transact-SQL (2000)

separating data in a field

Author

Topic

u2p_inst
Yak Posting Veteran

78 Posts

Posted - 2003-05-10 : 07:46:58

i have data in Name_friend field is "Michael Ananda Sarch"
how to separating the the text and the output of query look like this:

Name_friend | firstname | middlename | lastname
Michael Ananda Sinch Michael Ananda Sinch

thx

oh

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-05-10 : 10:55:07

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=24277

Ignore the "friendly banter" between Jay and Crespo and you'll see some ideas in there ... if that doesn't lead you to the answer, post again here any trouble you are having or any clarification that you need.

(of course, your data has spaces instead of commas, but the same basic idea).

- Jeff

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2003-05-10 : 10:55:51

Read this and also read the comments, then search the forums I'm sure there have probably been tweeks and improvements since the article.

http://www.sqlteam.com/item.asp?ItemID=2652

SamC
White Water Yakist

3467 Posts

Posted - 2003-05-10 : 16:32:28

One issue with Valter's solution (and Mr. Cross Join's) is the techniques covered don't discuss what to do if there's no middle name (e.g. John Smith). I suppose you could count the number of returned tokens, but the problem extends to whether names like Mary Jane are a single first name or a first and middle.

Then there are the Jr's and IIIs to worry about. The hard part breaking up a list of names is figuring out all the exceptions to the rules (or lack of rules).

Sam

Edited by - SamC on 05/10/2003 16:33:18

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2003-05-11 : 16:38:05

There are couple of ideas I've found useful for scoping out this sort of thing.
Given a table CREATE TABLE names (nm varchar(100) NOT NULL) and a sufficiently large tally table Numbers:

Find all the characters used in the names:


SELECT ASCII(c) AS code, c, COUNT(*) AS ct
FROM (
    SELECT SUBSTRING(nm, n, 1) AS c
    FROM names
    INNER JOIN junk..Numbers ON n BETWEEN 1 AND LEN(nm)
  ) AS A
GROUP BY c
ORDER BY ct DESC

Create a name 'signature' by reducing consecutive letters to a single character. This needs a bit more work if you have many names with accented letters, etc.
It's not necessary to create the view separately, but it may be useful for other queries -- for example, if you want to examine the names with unique signatures. The multiple character replacement scheme shown is good for up to 41 consecutive letter. To replace more requires another REPLACE: doing 21×'a' inside the 6×'a' one allow up to 461!


CREATE VIEW NameSig
AS
SELECT nm,
  REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
  REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
  REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
  REPLACE(REPLACE(REPLACE(nm, 'a','a'), 'b','a'), 'c','a'), 'd','a'), 'e','a'),
  'f','a'), 'g','a'), 'h','a'), 'i','a'), 'j','a'), 'k','a'), 'l','a'),
  'm','a'), 'n','a'), 'o','a'), 'p','a'), 'q','a'), 'r','a'), 's','a'),
  't','a'), 'u','a'), 'v','a'), 'w','a'), 'x','a'), 'y','a'), 'z','a'),
  'aaaaaa','a'), 'aaa','a'), 'aa','a'), 'aa','a') AS sig
FROM Names
GO

SELECT sig, COUNT(*)
FROM NameSig
GROUP BY sig
ORDER BY COUNT(*) DESC

On some sample data with about 85000 names, the latter returned:


a a             82462
a a a           919
a a'a           514
a a-a           407
a-a a           69
a a a a         5
a a-a-a         4
a-a a-a         3
a a a-a         3
a a a'a         3
a a-a-a-a       2
a a'a a         1
a'a a           1
a-a a'a         1
a' a            1

Edited by - Arnold Fribble on 05/11/2003 16:39:36

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-05-12 : 09:29:26

Very cool, Arnold ... i guess you could replace the a's with % after the generating that list, and then you could join the BACK to the data if you liked to see which rows match which pattern (using a "LIKE" join).

Neat idea .

- Jeff

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2003-05-14 : 21:12:58

Arnold, strikes again.

cheers

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources