Parse out the first 3 characters

Question

Parse out the first 3 characters

ronan.healy

SSCrazy

Points: 2232
More actions
August 11, 2014 at 2:52 am

#294354

Hi
how do I do I pull out the first three chars of a column using a derived column. I need to pull out first 3 chars and use them at a later time in the code

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

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

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 1

You use the LEFT function.

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

ronan.healy SSCrazy Points: 2232 More actions · Answer 2

ok thanks

and if the string is not all the same length I will probably hit problems ?

Phil Parkin SSC Guru Points: 246956 More actions · Answer 3

ronan.healy (8/11/2014)
ok thanks
and if the string is not all the same length I will probably hit problems ?

As long as the string is three or more characters, you should be fine.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

ronan.healy SSCrazy Points: 2232 More actions · Answer 4

I used this

Left(TaxLotDescription,3)

and its not working. I then looked into my string function and left is not there right is. why is that

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

Phil Parkin (8/11/2014)
ronan.healy (8/11/2014)
ok thanks
and if the string is not all the same length I will probably hit problems ?
As long as the string is three or more characters, you should be fine.

Even if the string is shorter than three characters, it's not an issue. LEFT will just return the original string.

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

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 6

ronan.healy (8/11/2014)
I used this
Left(TaxLotDescription,3)
and its not working. I then looked into my string function and left is not there right is. why is that

Ah, LEFT is probably introduced in a later version.

In that case, use SUBSTRING instead.

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

joeroshan SSChampion Points: 10377 More actions · Answer 7

in ssis 2005 you need to use SUBSTRING(ColumnName,1,3)

----------------------------------------------------------------------------------------------------------------------------------------------------
Roshan Joe

Jeff Moden -Forum Etiquette: How to post data/code on a forum to get the best help[/url]

ronan.healy SSCrazy Points: 2232 More actions · Answer 8

ronan.healy

SSCrazy

Points: 2232

August 11, 2014 at 3:30 am

#1736743

thanks that worked

Vimal Lohani SSCommitted Points: 1650 More actions · Answer 9

Use below example :

DECLARE @i TABLE( mychar VARCHAR(50)

,deriv as SUBSTRING(mychar,1,3) --Use this

,newderiv as left(mychar,3)); --Or use this

INSERT @i VALUES ('Steve'), ('Stephan'), ('Stephanie')

, ('Sterling'), ('Steppenwolf'),('Ab')

SELECT * FROM @i

Vimal LohaniSQL DBA | MCP (70-461,70-462)==============================The greatest barrier to success is the fear of failure ** Success is a journey not a destination**Think before you print, SAVE TREES, Protect Mother Nature

Phil Parkin SSC Guru Points: 246956 More actions · Answer 10

Koen Verbeeck (8/11/2014)
Phil Parkin (8/11/2014)
ronan.healy (8/11/2014)
ok thanks
and if the string is not all the same length I will probably hit problems ?
As long as the string is three or more characters, you should be fine.
Even if the string is shorter than three characters, it's not an issue. LEFT will just return the original string.

Thanks for the correction!

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Vimal Lohani SSCommitted Points: 1650 More actions · Answer 11

I didn't find any error with the code :

DECLARE @i TABLE( mychar VARCHAR(50)
,deriv as SUBSTRING(mychar,1,3) --Use this
,newderiv as left(mychar,3));--Or use this
INSERT @i VALUES ('Steve'), ('Stephan'), ('Stephanie')
, ('Sterling'), ('Steppenwolf'),('Ab'),('Abc')
SELECT * FROM @i
--RESULT :
(7 row(s) affected)
mychar deriv newderiv
-------------------------------------------------- ----- --------
Steve Ste Ste
Stephan Ste Ste
Stephanie Ste Ste
Sterling Ste Ste
Steppenwolf Ste Ste
Ab Ab Ab
Abc Abc Abc
(7 row(s) affected)

Vimal LohaniSQL DBA | MCP (70-461,70-462)==============================The greatest barrier to success is the fear of failure ** Success is a journey not a destination**Think before you print, SAVE TREES, Protect Mother Nature

MMartin1 One Orange Chip Points: 27795 More actions · Answer 12

Vimal Lohani (8/17/2014)
I didn't find any error with the code :
DECLARE @i TABLE( mychar VARCHAR(50)
,deriv as SUBSTRING(mychar,1,3) --Use this
,newderiv as left(mychar,3));--Or use this
INSERT @i VALUES ('Steve'), ('Stephan'), ('Stephanie')
, ('Sterling'), ('Steppenwolf'),('Ab'),('Abc')
SELECT * FROM @i
--RESULT :
(7 row(s) affected)
mychar deriv newderiv
-------------------------------------------------- ----- --------
Steve Ste Ste
Stephan Ste Ste
Stephanie Ste Ste
Sterling Ste Ste
Steppenwolf Ste Ste
Ab Ab Ab
Abc Abc Abc
(7 row(s) affected)

You're running the above script in management studio. The question deals with the derived column SSIS transformation (used inside a data flow task). I also dont have the LEFT function here in SSIS 2008 either. So it must have come later.

----------------------------------------------------

Vimal Lohani SSCommitted Points: 1650 More actions · Answer 13

MMartin1 (10/31/2014)
Vimal Lohani (8/17/2014)
I didn't find any error with the code :
DECLARE @i TABLE( mychar VARCHAR(50)
,deriv as SUBSTRING(mychar,1,3) --Use this
,newderiv as left(mychar,3));--Or use this
INSERT @i VALUES ('Steve'), ('Stephan'), ('Stephanie')
, ('Sterling'), ('Steppenwolf'),('Ab'),('Abc')
SELECT * FROM @i
--RESULT :
(7 row(s) affected)
mychar deriv newderiv
-------------------------------------------------- ----- --------
Steve Ste Ste
Stephan Ste Ste
Stephanie Ste Ste
Sterling Ste Ste
Steppenwolf Ste Ste
Ab Ab Ab
Abc Abc Abc
(7 row(s) affected)
You're running the above script in management studio. The question deals with the derived column SSIS transformation (used inside a data flow task). I also dont have the LEFT function here in SSIS 2008 either. So it must have come later.

Please use substring.

Vimal LohaniSQL DBA | MCP (70-461,70-462)==============================The greatest barrier to success is the fear of failure ** Success is a journey not a destination**Think before you print, SAVE TREES, Protect Mother Nature

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 14

MMartin1 (10/31/2014)
Vimal Lohani (8/17/2014)
I didn't find any error with the code :
DECLARE @i TABLE( mychar VARCHAR(50)
,deriv as SUBSTRING(mychar,1,3) --Use this
,newderiv as left(mychar,3));--Or use this
INSERT @i VALUES ('Steve'), ('Stephan'), ('Stephanie')
, ('Sterling'), ('Steppenwolf'),('Ab'),('Abc')
SELECT * FROM @i
--RESULT :
(7 row(s) affected)
mychar deriv newderiv
-------------------------------------------------- ----- --------
Steve Ste Ste
Stephan Ste Ste
Stephanie Ste Ste
Sterling Ste Ste
Steppenwolf Ste Ste
Ab Ab Ab
Abc Abc Abc
(7 row(s) affected)
You're running the above script in management studio. The question deals with the derived column SSIS transformation (used inside a data flow task). I also dont have the LEFT function here in SSIS 2008 either. So it must have come later.

The LEFT string function is first found in SSIS 2012.