Query Required splition of home address column in street name & house #

• ALI100

  SSC-Addicted

  Points: 471

  More actions

  July 7, 2012 at 2:41 am

  #263488

  I have a columna name "home address" in which i have data like that ,mean there is house# with the street name,I want to fetch the column in house# & streetname

  13480 lone point lane

  4420 Douglas Ave. E

  10655 birch St

  I want to show separate the house# & streetname

  so i need

  house# streetname

  13480 lone point lane

  4420 Douglas Ave

  10655 birch St

• Ron McCullough

  SSC Guru

  Points: 63877

  More actions

  July 7, 2012 at 6:25 am

  #1509956

  This might be what you require.

  CREATE TABLE #Address(HomeAddress VARCHAR(75))

  INSERT INTO #Address

  SELECT '13480 lone point lane' UNION ALL

  SELECT '4420 Douglas Ave. E' UNION ALL

  SELECT '10655 birch St'

  SELECT SUBSTRING(HomeAddress,1,(CHARINDEX (' ',HomeAddress,1)-1)) AS 'House #'

  ,SUBSTRING(HomeAddress,(CHARINDEX (' ',HomeAddress,1)+1), DATALENGTH(HomeAddress)) FROM #Address

  Result:

  House#street name

  13480lone point lane

  4420Douglas Ave. E

  10655birch St

  If everything seems to be going well, you have obviously overlooked something.

  Ron
  
  Please help us, help you -before posting a question please read[/url]
  Before posting a performance problem please read[/url]