Trim field after first space
-
I am trying to bring data from our ERP system (DB2) into our Quality package (SQL 2000). The field I am bring over is populated with an item number and description. I need to trim out everything after the space. Any Ideas. I am currently trying to attempt this in a DTS or T-SQL
Example
12356 Square 1/2 inch Washer - need to end up with 12356
123 Round 1 inch Washer - need to end up with 123
123x45 Round Blue Washer - need to end up with 123x45
Any help would be appreciated
Thanks
Jedi
-
Try this -
declare @text varchar(100)
set @text = '12356 Square 1/2 inch Washer'
select substring(@text, 1, charindex(' ',@text))
--Steve
-
Thank You - Perfect
Jedi
-
I have seen people ask how to convert statements to usable UDFs, so here is this one that I converted.
ALTER FUNCTION dbo.Trim_Field
(
@Value NVARCHAR(3000)
,@Trim_Characters NVARCHAR(10) = ' '
,@Starting_Characters NVARCHAR(10) = ''
 
/*
trims a string @Value at the first occurance of
@Trim_Characters after the occurance of @Starting_Characters
default for Trim is space and if no starting character is supplied
then starts search at beginning of string
Use: Select Name,dbo.Trim_Field(Name,default,default) as Trimmed_Name FROM Table
*/
RETURNS NVARCHAR(3000)
AS
BEGIN
DECLARE @Starting_Position int
DECLARE @Value_Return NVARCHAR(3000)
SET @Starting_Position = 1
IF @Starting_Characters <> ''
BEGIN
SET @Starting_Position = charindex(@Starting_Characters,@Value)
END
SET @Value_Return = substring(@Value, @Starting_Position, charindex(@Trim_Characters,@Value))
RETURN @Value_Return
END
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply