• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 287
  • Last Modified:

3 quick questions

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
musclejack
Asked:
musclejack
  • 5
  • 4
  • 2
  • +5
3 Solutions
 
JulianvaCommented:
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
 
stevetheskiCommented:
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
 
adatheladCommented:
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
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
nmcdermaidCommented:
Heres another way to strip your phone number


SELECT REPLACE(REPLACE(REPLACE(REPLACE(Phone,'(',''),')',''),'-',''),' ','') FROM yourtable
0
 
doobdaveCommented:
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
 
musclejackAuthor Commented:
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
 
musclejackAuthor Commented:
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
 
stevetheskiCommented:
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
 
adatheladCommented:
>> 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
 
PePiCommented:
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
 
Scott PletcherSenior DBACommented:
#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
 
musclejackAuthor Commented:
For Question2, is there another way to get the firstname from the field fullname.  Someone might have 2 words for their firstname.  
0
 
stevetheskiCommented:
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
 
musclejackAuthor Commented:
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
 
stevetheskiCommented:
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
 
Scott PletcherSenior DBACommented:
#2:
SELECT REPLACE([Full Name], [Last Name], '') AS [First Name]
0
 
stevetheskiCommented:
Scotts will work just as well.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 5
  • 4
  • 2
  • +5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now