Notifications
Clear all
Topic starter
We have to report the street number in a separate field. how might I do this so I can get the number in one field and the street in another field.
Thank you newbie SQL
Posted : January 21, 2021 2:04 am
This simple query on the patientprofile table shows you how to get the street number, assuming everything to the left of the first space in the field ADDRESS1 is the street number and use the rest of the field for the streeet:
select substring(address1,1,charindex(' ',address1) -1) as number,charindex(' ',address1) as space, substring(address1,charindex(' ',address1) +1,len(address1) - charindex(' ',address1) +1)as street, address1 from patientprofile
First column is the street number, second column is the numerical representation of where the first space in ADDRESS1 is, third column is the rest of the field, and the fourth column is ADDRESS1.
Good luck!
Posted : January 21, 2021 12:04 pm
Topic starter
Thank you very much. that worked perfectly.
Posted : January 21, 2021 11:59 pm
