?
Solved

3 quick questions

Posted on 2005-05-10
17
Medium Priority
?
277 Views
Last Modified: 2010-03-19
i have 3 quick questions:

1.  how to update  table A from database A in server A from table B from database B in server B

2.  I have a table that contains 2 columns,  "FullName" and "LastName".  E.g.  Muscle Jack, Jack.  How can I use any string functions to get only the first name from the field "FULLName"

3.  I have a column "Phone" in a table.
E.g.
(123) 222-2222
222-222-8888
888 333 4567

How can I remove all the space and other characters, and just leave the numbers, e.g. 1232222222, 2222228888, 8883334567

0
Comment
Question by:musclejack
  • 5
  • 4
  • 2
  • +5
17 Comments
 
LVL 8

Assisted Solution

by:Julianva
Julianva earned 200 total points
ID: 13975410
Question 1

You first need to link the servers - create a linked server
in enterprise manager

then do the update query like this


Question 2
say you wanted to get the name Muscle only
1,7 - start at 1 cursor before M and  7 characters long
select substring('Muscle Jack', 1,7)

Question 3

The following stored procedure does a search and replace

CREATE PROCEDURE replace_substring (
  @search_value varchar(128) = null,
  @replace_value varchar(128) = null
)
AS
DECLARE
  @execstr varchar(1000),
  @objectname sysname,
  @colname sysname

SET NOCOUNT ON
IF @search_value IS NULL
  BEGIN
    RAISERROR ('You must specify the value to search', 16, 1)
    RETURN
  END
IF @replace_value IS NULL
  BEGIN
    RAISERROR ('You must specify the value to replace', 16, 1)
    RETURN
  END

DECLARE tb_fetch_cursor CURSOR FOR
  SELECT name FROM sysobjects WHERE type = 'U'
OPEN tb_fetch_cursor
FETCH NEXT FROM tb_fetch_cursor INTO @objectname
WHILE (@@fetch_status <> -1)
  BEGIN
    DECLARE col_fetch_cursor CURSOR FOR
      SELECT name FROM syscolumns WHERE id = OBJECT_ID(@objectname) AND type IN
        (SELECT type FROM systypes WHERE name = 'char' OR name = 'nchar' OR name = 'varchar' OR name = 'nvarchar')
    OPEN col_fetch_cursor
    FETCH NEXT FROM col_fetch_cursor INTO @colname
    WHILE (@@fetch_status <> -1)
      BEGIN
        SELECT @execstr = 'IF EXISTS (SELECT * FROM ' + @objectname + ' WHERE ' + @colname + ' like ''%' + @search_value + '%'') BEGIN SELECT ''' + @objectname + ''' as tbname, ''' + @colname + ''' as colname PRINT '''' UPDATE ' + @objectname + ' SET ' + @colname + ' = REPLACE(' + @colname + ',''' + @search_value + ''',''' + @replace_value + ''')  WHERE ' + @colname + ' like ''%' + @search_value + '%''  END'
        EXEC (@execstr)
        FETCH NEXT FROM col_fetch_cursor INTO @colname
      END
    DEALLOCATE col_fetch_cursor
    FETCH NEXT FROM tb_fetch_cursor INTO @objectname
  END
DEALLOCATE tb_fetch_cursor

how to use the proc

exec replace_substring
@search = '('
@replace = ''

or
exec replace_substring
@search = '-'
@replace = ''

or

exec replace_substring
@search = ' '
@replace = ''



0
 
LVL 4

Expert Comment

by:stevetheski
ID: 13975447
1) in enterprise manager expand security and right click on linked server it will take you through  a wizard.  
once the server is linked you can query the table from a to b using full object notation e.g. DatabaseB.dbo.tablename

2)
SELECT LTRIM(
SUBSTRING('Muscle Jack, Jack',
--first position after the comma
 CHARINDEX(',','Muscle Jack, Jack')+1,
--length of firstname
 len('Muscle Jack, Jack') - CHARINDEX(',','Muscle Jack, Jack')+1
))

3)
make this into a function and you have a cheezy little give me only numerics

BEGIN

declare @x int,
@tempstr varchar(50),
@origString varchar(50);

set @origString = '(123) 222-2222'
set @tempstr = ''
set @x = len(@origString)
WHILE @x > 0
      BEGIN
            --Replace the non Keep instance with empty string
            if (ascii(substring(@origString,@x,1)) BETWEEN 48 and 57)
                  set @tempstr = @Tempstr + substring(@origString,@x,1)
            --find next instance of non Keep instance
            SET @x = @x-1
      END

      Select REVERSE(@tempstr)
END
0
 
LVL 23

Expert Comment

by:adathelad
ID: 13975984
Just to give a generic solution to question 2:

SELECT LEFT(FullName, CHARINDEX(' ', FullName + ' ')) AS FirstName
FROM YourTable

This will find the 1st name by searching for the first blank space in the FullName  (i.e. that separates the first + last name).
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 30

Expert Comment

by:nmcdermaid
ID: 13976524
Heres another way to strip your phone number


SELECT REPLACE(REPLACE(REPLACE(REPLACE(Phone,'(',''),')',''),'-',''),' ','') FROM yourtable
0
 
LVL 8

Expert Comment

by:doobdave
ID: 13976609
Just to add my 2 pence's worth:

Q2: The suggested answers will work, however I would take a different approach, but I'm not sure if this is possible in your case as you may not be in a position to modify the table:

1) Get Rid of FullName column.
    Reason: Duplication - You are effectively storing the lastnametwice, once in the LastName column, and once as part of the FullName column. I'm not sure how the data is inserted into this table, but it could mean that someone manually entering the info will mistype the surname in one or the other and you end up with inconsistent data.

2) Create a FirstName column.
    Reason: You can still get the FullName in any SELECT statement (SELECT LastName + ' ' + FirstName AS FullName FROM ...). Or you can set up a view to do this. This will result in a more normalised database structure, with less potential for data entry errors.

Regards,

David
0
 

Author Comment

by:musclejack
ID: 13977566
HI adathelad,

For Question2, is there another way to get the firstname from the field fullname.  Someone might have 2 words for their firstname.
0
 

Author Comment

by:musclejack
ID: 13977607
hi nmcdermaid,

your function for #3 work well.  but i need to remove a '+' character in some rows. could u show me how to do that
0
 
LVL 4

Expert Comment

by:stevetheski
ID: 13977826
muscle jack

Do you know sql at all?

if you want to use nmcdermaid example juat add another replace.
do you not understand the idea i had for #3

I agree fully wwith doobdave  about the table redesign

also
my example will work for any comma delimited name  in example #2 and it doesn't matter if they have 12 names as a first and 12 as a last.

Steve
0
 
LVL 23

Expert Comment

by:adathelad
ID: 13978022
>> For Question2, is there another way to get the firstname from the field fullname.  Someone might have 2 words for  their firstname.<<

Yes, try this:

SELECT LEFT(FullName, LEN(FullName) - CHARINDEX(' ', reverse(' ' + FullName))) AS FirstNames
FROM YourTable
0
 
LVL 6

Expert Comment

by:PePi
ID: 13980153
i would heed doobdave's advise. Separate the First name and the last name in 2 different fields. All the answers provided for Q2 will be useless if let's say a person's name is JOSE JUAN Z. RAMIREZ DE CARTAGENA. This person's last name is RAMIREZ DE CARTAGENA and most Latin names have 2 first names i.e. JOSE JUAN.
0
 
LVL 70

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 1800 total points
ID: 13981339
#3:
The function below will strip *all* non-numeric characters, no matter what they are, and with efficiency.
Naturally change the length from VARCHAR(20) to whatever you need (increase or decrease).


CREATE FUNCTION strip_nonnumeric (@string VARCHAR(20))
RETURNS VARCHAR(20)
AS
BEGIN
WHILE PATINDEX('%[^0-9]%', @string) > 0
BEGIN
      SET @string = STUFF(@string, PATINDEX('%[^0-9]%', @string), 1, '')
END --WHILE
RETURN @string
END --FUNCTION



SELECT dbo.strip_nonumeric(phone)
FROM anyTable
0
 

Author Comment

by:musclejack
ID: 14007916
For Question2, is there another way to get the firstname from the field fullname.  Someone might have 2 words for their firstname.  
0
 
LVL 4

Expert Comment

by:stevetheski
ID: 14007959
same answer for number 2 as my original Answer
It doesn't matter how many names you have for first or last if you have a comma delimiter
try running the query below

declare @theName varchar(500)
set @theName = 'Muscle Jack He may have this many names in his last name, Jack might have this many first names too it doesnt matter to this query as long as there is a comma as the delimiter'
SELECT LTRIM(
SUBSTRING(@theName,
--first position after the comma
 CHARINDEX(',',@theName)+1,
--length of firstname
 len(@theName) - CHARINDEX(',',@theName)+1
))

0
 

Author Comment

by:musclejack
ID: 14010237
there is no comma delimiter on the field!!! what i want to do is to use a function to get firstname by doing this Field - 'Full Name' - Field - 'Last Name' = First Name
0
 
LVL 4

Expert Comment

by:stevetheski
ID: 14011006
well if there is no delimiter between fiorst name and last name it is impossible to determine which is the first name and which is the last name.

example
fullName
----------------
lastname1 lastname2 lastname3 firstname1 firstname2 firstname3
lastname1 lastname2 firstname1 firstname2
lastname firstname1 firstname2 firsstname3
lastname firstname

how are we to know where the last name ends and the first name begins?
how do we know how many names are in the first name and the last name?

your question as posted
2.  I have a table that contains 2 columns,  "FullName" and "LastName".  E.g.  Muscle Jack, Jack.  How can I use any string functions to get only the first name from the field "FULLName"

Note you have a comma delimiting the names

now you say you want to get the first name if you know the last name????
'Full Name' - Field - 'Last Name' = First Name
what id Field?

if you habe a lastame column you can do it like so

declare @lname varchar(50)
declare @fullname varchar(50)
set @lname = 'sawyer brown'
set @fullname = 'sawyer brown jim bob boy'

SELECT
SUBSTRING(@fullName,
 len(@lname)+2,
 len(@fullName) - len(@lname)
)

0
 
LVL 70

Accepted Solution

by:
Scott Pletcher earned 1800 total points
ID: 14011111
#2:
SELECT REPLACE([Full Name], [Last Name], '') AS [First Name]
0
 
LVL 4

Expert Comment

by:stevetheski
ID: 14011271
Scotts will work just as well.
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have a large data set and a SSIS package. How can I load this file in multi threading?
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Suggested Courses

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question