August 10, 2015 at 4:28 am
HI,
I need to create a function that takes one input parameter and returns five output.
The function must clean a street name.
E.g:
Function receives this street number, and must be divided into separate columns.
-346A
returns -in a column, 346 in a column and A in a column.
Can someone help me with this?
Thank you
August 10, 2015 at 4:37 am
It's not clear whether you want five columns or three. This will work for your example:
DECLARE @StreetName varchar(100)
SET @StreetName = '-346A'
SELECT
col1 = LEFT(@StreetName,1)
,col2 = SUBSTRING(@StreetName,2,3)
,col3 = RIGHT(@StreetName,1)
John
August 10, 2015 at 5:09 am
Hi John,
Thank you very much, I want it to give me 5 columns.
I have been given a task and I'm a beginner in SQL.
August 10, 2015 at 5:20 am
Happyt 43606 (8/10/2015)
returns -in a column, 346 in a column and A in a column.
What do you want in the other two columns? Is this a homework question, by any chance?
John
August 10, 2015 at 5:48 am
John Mitchell-245523 (8/10/2015)
Happyt 43606 (8/10/2015)
returns -in a column, 346 in a column and A in a column.What do you want in the other two columns? Is this a homework question, by any chance?
John
As John shared the solution earlier, i have just updated it for your need. what i have understand is that you need a column for each character in the street you have given. This code assume that the street will always be of 5 characters. Following code will do.
DECLARE @StreetName varchar(100)
SET @StreetName = '-346A'
SELECT
col1 = SUBSTRING(@StreetName,1,1)
,col2 = SUBSTRING(@StreetName,2,1)
,col3 = SUBSTRING(@StreetName,3,1)
,col4 = SUBSTRING(@StreetName,4,1)
,col5 = SUBSTRING(@StreetName,5,1)
Read to understand the working of SUBSTRING
hope it helps
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply