Seperate a string into two fields

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

How do I seperate the following in a sql query:

Field1: 123456789-JohnDoe
into
Field2: 123456789
Field3: JohnDoe

Here's the formula I use in access query, but I want to use them in a sql
pass-through query.

Field2: Left([Field1],InStr(1,[Field1],"-")-1)
Field3:
Mid([Field1],InStr(1,[Field1],"-")+1,(Len([Field1])-InStr(1,[Field1],"-"))+1)

Thanks a bunch!!
 
If I understand your question correctly, try this:
SELECT Table1.Field1, Left(Table1!Field1,InStr(1,Table1!Field1,"-")-1) AS
Expr1,
Mid(Table1!Field1,InStr(1,Table1!Field1,"-")+1,(Len(Table1!Field1)-InStr(1,Table1!Field1,"-"))+1) AS Expr2
FROM Table1;
Regards
 
further if you want to update Fiels 2 and 3:
UPDATE Table1 SET Table1.Field2 =
Left(Table1!Field1,InStr(1,Table1!Field1,"-")-1), Table1.Field3 =
Mid(Table1!Field1,InStr(1,Table1!Field1,"-")+1,(Len(Table1!Field1)-InStr(1,Table1!Field1,"-"))+1);
Regards
 
InStr is not a function in SQL, unless I am doing something wrong. I'm
creating a SQL Pass Through query in access. The formula below this message
works in ms access query. I believe I need to use substring function but I
don't know how to translate the orignal formula to use substring. Thanks!

JackL said:
further if you want to update Fiels 2 and 3:
UPDATE Table1 SET Table1.Field2 =
Left(Table1!Field1,InStr(1,Table1!Field1,"-")-1), Table1.Field3 =
Mid(Table1!Field1,InStr(1,Table1!Field1,"-")+1,(Len(Table1!Field1)-InStr(1,Table1!Field1,"-"))+1);
Regards

G said:
How do I seperate the following in a sql query:

Field1: 123456789-JohnDoe
into
Field2: 123456789
Field3: JohnDoe

Here's the formula I use in access query, but I want to use them in a sql
pass-through query.

Field2: Left([Field1],InStr(1,[Field1],"-")-1)
Field3:
Mid([Field1],InStr(1,[Field1],"-")+1,(Len([Field1])-InStr(1,[Field1],"-"))+1)

Thanks a bunch!!
Click to expand...
Click to expand...
 
I found the website that helped me. I needed to use charindex and substring
functions. here's the website:
http://www.databasejournal.com/features/mssql/article.php/3071531

Thanks for helping Jack!!

JackL said:
further if you want to update Fiels 2 and 3:
UPDATE Table1 SET Table1.Field2 =
Left(Table1!Field1,InStr(1,Table1!Field1,"-")-1), Table1.Field3 =
Mid(Table1!Field1,InStr(1,Table1!Field1,"-")+1,(Len(Table1!Field1)-InStr(1,Table1!Field1,"-"))+1);
Regards

G said:
How do I seperate the following in a sql query:

Field1: 123456789-JohnDoe
into
Field2: 123456789
Field3: JohnDoe

Here's the formula I use in access query, but I want to use them in a sql
pass-through query.

Field2: Left([Field1],InStr(1,[Field1],"-")-1)
Field3:
Mid([Field1],InStr(1,[Field1],"-")+1,(Len([Field1])-InStr(1,[Field1],"-"))+1)

Thanks a bunch!!
Click to expand...
Click to expand...
 
A Pass through query to an SQL Server would look something like the
following.

SELECT Field1
, substring(Field1, 1, CharIndex('-',Field1)-1) as SSN
, substring(Field1,CharIndex('-',Physician_LastName) + 1,50) as TheName
FROM YourTable
WHERE Field1 like '%,%'
 
Back
Top