SQL Concat 1

[MichaelF81]

I have a table with a full_name field, name_last and name_first fields.

I need to take the full_name field (LName, FName MI) and split the LName into name_last and the FName MI into name_first.

Thoughts?




"Adults are just obsolete children and the hell with them." - Dr. Seuss

 

[gmmastros]

Take a look at the CharIndex function in Books On Line. As long as every record seperates the last name from the first name with a comma, the charindex function will help you to split the names.

Ex:

Declare @Temp VarChar(100)

Set @Temp = 'Bush, George W.'

Select Left(@Temp, CharIndex(',', @Temp) - 1), LTrim(Right(@Temp, Len(@Temp) - CharIndex(',', @Temp)))

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[MichaelF81]

George,

Thanks a lot, but it still leaves on question, how do I get the split values into new rows? I was using the CharIndex value earlier, but I could not figure our how to get it to set new values.




"Adults are just obsolete children and the hell with them." - Dr. Seuss

 

[gmmastros]

Make sure you have a good backup before running this.

Update Table
Set    name_last = Left(full_name, CharIndex(',', full_name) - 1), 
       name_first = LTrim(Right(full_name, Len(full_name) - CharIndex(',', full_name)))

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[MichaelF81]

Thanks mate, this is a development DB it is not an issue if I blow it up!




"Adults are just obsolete children and the hell with them." - Dr. Seuss

 

[MichaelF81]

Server: Msg 536, Level 16, State 3, Line 1
Invalid length parameter passed to the substring function.
The statement has been terminated.

"Adults are just obsolete children and the hell with them." - Dr. Seuss

 

[gmmastros]

Just as I feared. You have a record where there is no comma in the full_name column.

Select *
From   Table
Where  CharIndex(',', full_name) = 0

How do you want to handle these?

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[MichaelF81]

OMG I am a putz, hahaha, the first row is the headers, HAHAHA.




"Adults are just obsolete children and the hell with them." - Dr. Seuss

 

[MichaelF81]

How would I Script something that did the following

[b]if[/b]
Select *
From   afm.em
Where  (CharIndex(',', em_id) = 0)

[b]THEN[/b]

update afm.em
set em_id = rtrim(em_id) + ', ' + rtrim(em_id)
Where  (CharIndex(',', em_id) = 0)

"Adults are just obsolete children and the hell with them." - Dr. Seuss

 

[gmmastros]

Like this???

if Exists(
    Select *
    From   afm.em
    Where  (CharIndex(',', em_id) = 0)
    )
  Begin
    update afm.em
    set em_id = rtrim(em_id) + ', ' + rtrim(em_id)
    Where  (CharIndex(',', em_id) = 0)
  End

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[MichaelF81]

hrm, so I could use that same thing for creating one of my tables

if not exists (
select * from A_IMP
)
Begin
CREATE TABLE A_Imp (
  full_name varchar (150) ,
  em_id varchar (150) ,
  dp_name varchar (150) ,
  dp_id varchar (150) ,
  job_title varchar (150) ,
  u_std_desc varchar (150) ,
  em_std varchar (150) ,
  u_hiredate varchar (150) ,
  lname varchar (150) ,
  fname varchar (150) ,
  dv_Id varchar (150)
)
Go
End

"Adults are just obsolete children and the hell with them." - Dr. Seuss

 

[gmmastros]

Nope. Can you guess why?

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[gmmastros]

I should have said...

You can use the concept, but the syntax you show won't work.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[MichaelF81]

I am not sure. I also tried

(select * from A_Imp)
And not exists
(CREATE TABLE A_Imp (
  full_name varchar (150) ,
  em_id varchar (150) ,
  dp_name varchar (150) ,
  dp_id varchar (150) ,
  job_title varchar (150) ,
  u_std_desc varchar (150) ,
  em_std varchar (150) ,
  u_hiredate varchar (150) ,
  lname varchar (150) ,
  fname varchar (150) ,
  dv_Id varchar (150)
))
Go

but that did not work either...




"Adults are just obsolete children and the hell with them." - Dr. Seuss

 

[gmmastros]

The problem is that you are trying to select from the table when the table could (possibly) not exist. So, you need to find another way to determine if the table exists before creating it.

if not exists (
    [!]select * 
    from   Information_Schema.Tables 
    Where  Table_Name='A_Imp' 
           and Table_Type='Base Table' [/!])
Begin
CREATE TABLE A_Imp (
  full_name varchar (150) ,
  em_id varchar (150) ,
  dp_name varchar (150) ,
  dp_id varchar (150) ,
  job_title varchar (150) ,
  u_std_desc varchar (150) ,
  em_std varchar (150) ,
  u_hiredate varchar (150) ,
  lname varchar (150) ,
  fname varchar (150) ,
  dv_Id varchar (150)
)
End

Information_Schema.Tables is a system supplied view that contains information regarding your tables. There is also an Information_Schema.Columns that contains information about the columns in each of your tables.

Bottom line is that Information_Schema.Tables will always exist in the database, so selecting from it won't give you an error. Of course, filtering on table name and table type will determine whether the record exists. You won't get an error selecting from the view. You'll either get a record or you won't, which is exactly what Exists checks for.

Also notice that I included Table_Type = 'Base Table'. Information_Schema.Tables also includes information about any view that you created in your database.

Make sense?

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[MichaelF81]

George,

This makes a lot of sense. I appreciate the info, I did not just copy and paste the code into my DTS package, I truely know what you did, and I appreciate it!

Thanks again

<!-- Michael -->

/* p.s. I do more web programming than SQL, so I appreciate the SQL help and knowledge */




"Adults are just obsolete children and the hell with them." - Dr. Seuss