How to split column text to another column?

How to split delimited text in a column, fetch respective ID from another table and store in a new column

• I have a column i.e. p_author in sql server table named as sub_aminer_paper containing values of single or multiple authors having data type text separated by semi-colons i.e. ; Now I have to assign the respective id's to this column values i.e. p_author where id's for these authors are stored in another table named sub_aminer_author in the column aid. The view of both tables are attached as images. Table sub_aminer_author http://i.stack.imgur.com/E0msK.png Table sub_aminer_paper http://i.stack.imgur.com/zKdmi.png I have highlighted aid and name columns in sub_aminer_author table and p_author column in sub_aminer_paper table. Now I have to add a new column named p_authors_id in sub_aminer_paper table and fill it with respective aid from sub_aminer_author table. I am attaching the sample data in csv format for both table (only concerned columns data) Here are the links to csv files https://drive.google.com/open?id=0B6ihKgOLimL0cEtyYVR0WExBbFk https://drive.google.com/file/d/0B6ihKgOLimL0MUhHZFlpLThTZzA/view?usp=sharing And for the table creation, here is the code -- CREATE TABLE [dbo].[sub_aminer_paper]( [pid] [int] NULL, [p_author] [varchar](max) NULL) CREATE TABLE [dbo].[sub_aminer_author]( [aid] [int] NULL, [name] [varchar](max) NULL) Please help and thanks in advance!

• Answer:

  As you mention above the #sub_aminer_author contains first three character as number so this query will work according to requiment select left(name,3) as aid , substring( [name], 4 ,len(name) )as name from #sub_aminer_author More over for sub_aminer_paper you have given wrong CSV file kindly check it..as i can see the example it contains 5 character as numeric so this query wil work accordingly.. select left(name,5) as aid , substring( [name], 6 ,len(name) )as name from #sub_aminer_paper Please find the updates query for #sub_aminer_paper SELECT left (name,(PATINDEX('%[^0-9]%',name)-1) ) as pid, Substring(name, Patindex('%[^0-9]%', [name]), Len(name)) as p_author FROM #sub_aminer_paper