How to split the Records?

Question

Post reply

How to split the Records?

Charmer

SSChampion

Points: 14903
More actions
December 20, 2011 at 2:05 am

#144680

Hi Friends ,
i have column name CrossStreets in an excel sheet which will be having datas like "W MENOMONEE RIVER PKWY"....
i need to split the records into 3 columns like
Prefix Streetname Type
W MENOMONEE RIVERPKWY
how can i get his done?
Thanks,
Charmer

Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Henrico Bekker One Orange Chip Points: 27652 More actions · Answer 1

Text to Columns.

in the menu under "Data", "Text to Columns"

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle

Charmer SSChampion Points: 14903 More actions · Answer 2

Henrico Bekker (12/20/2011)
Text to Columns.
in the menu under "Data", "Text to Columns"

sorry i gave a wrong information...my source is .mdb....

Thanks,
Charmer

Henrico Bekker One Orange Chip Points: 27652 More actions · Answer 3

Charmer (12/20/2011)
Henrico Bekker (12/20/2011)
Text to Columns.
in the menu under "Data", "Text to Columns"
sorry i gave a wrong information...my source is .mdb....

MS ACCESS?

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle

Charmer SSChampion Points: 14903 More actions · Answer 4

Henrico Bekker (12/20/2011)
Charmer (12/20/2011)
Henrico Bekker (12/20/2011)
Text to Columns.
in the menu under "Data", "Text to Columns"
sorry i gave a wrong information...my source is .mdb....
MS ACCESS?

yes pal...

Thanks,
Charmer

Koen Verbeeck SSC Guru Points: 259197 More actions · Answer 5

Charmer (12/20/2011)
Hi Friends ,
i have column name CrossStreets in an excel sheet which will be having datas like "W MENOMONEE RIVER PKWY"....
i need to split the records into 3 columns like
Prefix Streetname Type
W MENOMONEE RIVERPKWY
how can i get his done?

Very difficult. How do you know upfront if the streetname contains a space or not?

I guess you don't. Which means you can't use the space as a delimiter.

My guess is (as you practically don't give us any information) that prefix is only one character long. You can substract that using LEFT(myString,1) or using SUBSTRING in a derived column.

To find Type, you can reverse the string, look for the first space using FINDSTRING, get the position and then use SUBSTRING again to substract it from the string.

So, you have the position of the first space (one character after prefix), the position of the last space, which means you can substract the streetname using SUBSTRING and those two positions.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Charmer SSChampion Points: 14903 More actions · Answer 6

Koen Verbeeck (12/20/2011)
Charmer (12/20/2011)
Hi Friends ,
i have column name CrossStreets in an excel sheet which will be having datas like "W MENOMONEE RIVER PKWY"....
i need to split the records into 3 columns like
Prefix Streetname Type
W MENOMONEE RIVERPKWY
how can i get his done?
Very difficult. How do you know upfront if the streetname contains a space or not?
I guess you don't. Which means you can't use the space as a delimiter.
My guess is (as you practically don't give us any information) that prefix is only one character long. You can substract that using LEFT(myString,1) or using SUBSTRING in a derived column.
To find Type, you can reverse the string, look for the first space using FINDSTRING, get the position and then use SUBSTRING again to substract it from the string.
So, you have the position of the first space (one character after prefix), the position of the last space, which means you can substract the streetname using SUBSTRING and those two positions.

yeh koen, i knew it is very very difficult...i need to get this done but i don't know how to do this..the client has given me the input like a shit...:angry:........any way thank you for your timely ideas...i'l try with what you guided me...

Thanks,
Charmer