Transact-sql code does not work. HELP!
Hi all, I am new to transact-sql and hoped that someone here might be able to help. I have a db with a field called "part" ... part contains text in the format:
xxxxx-xx-xxxxx
(the number of x's before or after each hyphen vary) such that I could have xxx-xxxxx-xxx as the part.
Someone wrote this transact-sql to take my current "part" field and move it to three different part fields ... a,b,c
If xxxxx-xx-xxxxx was the part in the original db, then a would contain xxxxx .. b contains xx ... c contains xxxxx
Make sense? Here is the code. It doesn't work and I can't seem to figure out why! It looks like it is just trying to find the hyphens and cut out the text from that... but it fails with an error that says "invalid length parameter passed to the substring function". Note, if I take off the "-1" from each line below... then the function works but produces the wrong data.
char(45) = "-" (hyphen)
UPDATE [dbo].[parts] SET
a = SUBSTRING(part,1,CHARINDEX(CHAR(45),part)-1),
b = SUBSTRING(part,CHARINDEX(CHAR(45),part)+1,CHARINDEX(CHAR(45),RIGHT(part,LEN(part)-CHARINDEX(CHAR(45),part)))-1),
c = SUBSTRING(part,CHARINDEX(CHAR(45),RIGHT(part,LEN(part)-CHARINDEX(CHAR(45),part)))+CHARINDEX(CHAR(45),part)+1,LEN(part))
GO
Does anyone know what I can do to fix this or is it much more difficult to do than what it seems?
Thanks!
Hi
It looks like your update expects to always have 2 hyphens in each value of the "part" column. The error is coming from those values
To find those not matching the pattern , run
select * from parts where part not like '%-%-%'
Your script willl work for
UPDATE [dbo].[parts] SET
a = SUBSTRING(part,1,CHARINDEX(CHAR(45),part)-1),
b = SUBSTRING(part,CHARINDEX(CHAR(45),part)+1,CHARINDEX(CHAR(45),RIGHT(part,LEN(part)-CHARINDEX(CHAR(45),part)))-1),
c = SUBSTRING(part,CHARINDEX(CHAR(45),RIGHT(part,LEN(part)-CHARINDEX(CHAR(45),part)))+CHARINDEX(CHAR(45),part)+1,LEN(part))
WHERE part like '%-%-%'
NB.
DECLARE @hyphen char(45)
SET @hyphen = "-"
UPDATE [dbo].[parts] SET
a = SUBSTRING(part,1,CHARINDEX(@hyphen,part)-1),
b = SUBSTRING(part,CHARINDEX(@hyphen,part)+1,CHARINDEX(@hyphen,RIGHT(part,LEN(part)-CHARINDEX(@hyphen,part)))-1),
c = SUBSTRING(part,CHARINDEX(@hyphen,RIGHT(part,LEN(part)-CHARINDEX(@hyphen,part)))+CHARINDEX(@hyphen,part)+1,LEN(part))
GO
Hi, Thanks for your input.
I tried your idea above which produces a running script... but all I get now is NULL in the a,b,c fields.
Thoughts?
I got yours to work also, except that all I get is NULL in a,b,c
I found the 670 records I have that don't have 2 hyphens and excluded them with the WHERE part like '%-%-%' statement.
Is there some way to printout what a b c are as it goes thru the records?
Thanks!!
Try this query.
Update [dbo].[parts]
Set A=ParseName(Replace(part, '-', '.'), 3),
B=ParseName(Replace(part, '-', '.'), 2),
C=ParseName(Replace(part, '-', '.'), 1)
Where len(part) - len(Replace(part, '-', '')) = 2
More info>
For example, I created table called Test.
part = char(20)
a = char(20)
I then ran the scripts above.
part contains KKKK-KK-KKKK
a should have contained KKKK but was null after the script ran.
Here's an example. You can copy/paste this in to Query Analyzer to see how it works.
Declare @Temp
Table (part varchar(100), a varchar(20), b varchar(20), c varchar(20))
insert into @Temp(Part) Values('xxxxxxx-ddd-rrrrr')
insert into @Temp(Part) Values('aaaa-b-cccccccccccc')
insert into @Temp(Part) Values('eeee-w-fffffffff')
Update @Temp
Set A=ParseName(Replace(part, '-', '.'), 3),
B=ParseName(Replace(part, '-', '.'), 2),
C=ParseName(Replace(part, '-', '.'), 1)
Where len(part) - len(Replace(part, '-', '')) = 2
Select * From @Temp
Are you speaking language other than T-SQL?. You keeping talking with some thing T-SQL won't understand. Such like :
part = char(20)
People speak T-SQL here, please tune in. Here is the test of the code I give you before:
DECLARE @Part char(12)
DECLARE @hyphen char(1)
SET @part ='XXX-XX-XXXXXX'
SET @hyphen = '-'
--UPDATE [dbo].[parts] SET
SELECT
a = SUBSTRING(@part,1,CHARINDEX(@hyphen,@part)-1),
b = SUBSTRING(@part,CHARINDEX(@hyphen,@part)+1,CHARINDEX(@hyphen,RIGHT(@part,LEN(@part)-CHARINDEX(@hyphen,@part)))-1),
c = SUBSTRING(@part,CHARINDEX(@hyphen,RIGHT(@part,LEN(@part)-CHARINDEX(@hyphen,@part)))+CHARINDEX(@hyphen,@part)+1,LEN(@part))
GO
It does not put null in a, b and c. As long as column a, b and c in the table have right size, it should work. If they are all null, then the part is null. Print out part to make sure it is not null.
Thanks for your help. This worked!!
Brian
