October 10, 2013 at 4:38 am
Hi All,
I have a table like this
CREATE TABLE [dbo].[template_practice](
[value1] [char](10) NOT NULL,
[value2] [char](9) NOT NULL,
CONSTRAINT [PK_template_practice] PRIMARY KEY CLUSTERED
(
[value1] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
and data inserted in the above table as
INSERT INTO [practicedb].[dbo].[template_practice]
([LTM_INV_CD]
,[LTM_TMP_CD])
VALUES
('AVINASH','ANAND')
GO
INSERT INTO [practicedb].[dbo].[template_practice]
([LTM_INV_CD]
,[LTM_TMP_CD])
VALUES
('AKASH','AKHILA')
GO
INSERT INTO [practicedb].[dbo].[template_practice]
([LTM_INV_CD]
,[LTM_TMP_CD])
VALUES
(' ',' ')
GO
INSERT INTO [practicedb].[dbo].[template_practice]
([LTM_INV_CD]
,[LTM_TMP_CD])
VALUES
(' ',' ')
GO
INSERT INTO [practicedb].[dbo].[template_practice]
([LTM_INV_CD]
,[LTM_TMP_CD])
VALUES
('KRISH','KRISHNA')
GO
INSERT INTO [practicedb].[dbo].[template_practice]
([LTM_INV_CD]
,[LTM_TMP_CD])
VALUES
('KRUPA','KANNA')
GO
so i want to replace the first character with 'M' whose letter is starting with 'K' only remaining values starting with other characters or null values are don't want to change.
I WRITE A QUERY LIKE THIS
use practicedb
go
DECLARE @find varchar(20)
SELECT @find='K'
UPDATE template_practice
SET VALUE1=Stuff(VALUE1, CharIndex(@find, VALUE1), Len(@find), 'M')
BUT WHEN I EXECUTE THIS IT SHOWS ERROR LIKE
Msg 515, Level 16, State 2, Line 4
Cannot insert the value NULL into column 'VALUE1', table 'practicedb.dbo.template_practice'; column does not allow nulls. UPDATE fails.
The statement has been terminated.
please tell me the stored procedure to clear my problem
October 10, 2013 at 4:45 am
You need to use the STUFF function. Have a go at writing the UPDATE query, and post back if there's anything in particular that you don't understand.
John
October 10, 2013 at 4:57 am
HI I UPDATED MY QUESTION PLEASE CHECK IT ONCE.
I HOPE NOW U CAN UNDERSTAND.
October 10, 2013 at 4:59 am
Add a WHERE clause. And your STUFF function can be simplified. Since it's always the first character you're changing, and you always change it to M, the final three parameters will be 1,1,'M'.
John
October 10, 2013 at 5:05 am
could u please write that.
October 10, 2013 at 6:25 am
Thank you so much JOHN i solved it.
October 10, 2013 at 6:47 am
Hi Try the following script it will work for your reuirement
CREATE TABLE [practicedb].[dbo].[template_practice](
[LTM_INV_CD] [char](10) NOT NULL,
[LTM_TMP_CD] [char](9) NOT NULL,
CONSTRAINT [PK_template_practice] PRIMARY KEY CLUSTERED
(
[LTM_INV_CD] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
INSERT INTO [practicedb].[dbo].[template_practice]
([LTM_INV_CD]
,[LTM_TMP_CD])
VALUES
('AVINASH','ANAND')
GO
INSERT INTO [practicedb].[dbo].[template_practice]
([LTM_INV_CD]
,[LTM_TMP_CD])
VALUES
('AKASH','AKHILA')
GO
/* Only one empty value we can insert into "LTM_INV_CD" since it is primary key so i commentted one script*/
INSERT INTO [practicedb].[dbo].[template_practice]
([LTM_INV_CD]
,[LTM_TMP_CD])
VALUES
(' ',' ')
GO
--INSERT INTO [practicedb].[dbo].[template_practice]
--([LTM_INV_CD]
--,[LTM_TMP_CD])
--VALUES
--(' ',' ')
--GO
INSERT INTO [practicedb].[dbo].[template_practice]
([LTM_INV_CD]
,[LTM_TMP_CD])
VALUES
('KRISH','KRISHNA')
GO
INSERT INTO [practicedb].[dbo].[template_practice]
([LTM_INV_CD]
,[LTM_TMP_CD])
VALUES
('KRUPA','KANNA')
GO
select * from [template_practice]
DECLARE @find varchar(20)
SELECT @find='K'
select
LTM_INV_CD, case when @find=SUBSTRING(LTM_INV_CD,CharIndex(@find, LTM_INV_CD), Len(@find)) then
Stuff(isnull(LTM_INV_CD,''),CharIndex(@find, LTM_INV_CD), Len(@find), 'M') else LTM_INV_CD end from template_practice
UPDATE template_practice
SET [LTM_INV_CD]=case when @find=SUBSTRING(LTM_INV_CD,CharIndex(@find, LTM_INV_CD), Len(@find)) then
Stuff(isnull(LTM_INV_CD,''),CharIndex(@find, LTM_INV_CD), Len(@find), 'M') else LTM_INV_CD end
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply