Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 String Manipulation

Author  Topic 

pakwichek
Starting Member

12 Posts
Posted - 2011-10-31 : 13:09:36
I want to extract data from a string into different fields in a table and contained in field called Filetype and table called RECEIVABLE. Here is example Strings contain in

"5435 Akwa Joseph Ndifon 200k 34514.pdf"
"5436 Njang 250k 34516.pdf"

Each string should parlsed into the following fields:
CustomerID, CustomerName, Amount Paid, and ReceiptNumber
5435 Akwa Joseph Ndifon 200000 34514
5436 Njang 250000 34516

please note 200k in the string means 200000
and 250K means 250000
where K = 1000 ( or replace by 000)


What T-SQL command can be used to do this, I will really appreciate any help or direction on this







peter Akwichek

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts
Posted - 2011-10-31 : 13:40:33
declare @s table (s varchar(1000), id int identity)
insert @s select '5435 Akwa Joseph Ndifon 200k 34514.pdf'
insert @s select '5436 Njang 250k 34516.pdf'

select CustomerID, CustomerName, [Amount Paid] = case when [Amount Paid] like '%k' then CONVERT(int,replace([Amount Paid],'k','')) * 1000 else CONVERT(int,[Amount Paid]) end, ReceiptNumber
from (
select CustomerID = SUBSTRING(s,1,s1),
CustomerName = SUBSTRING(s,s1+1,s2-s1),
[Amount Paid]= SUBSTRING(s,s2+1,s3-s2),
ReceiptNumber = SUBSTRING(s,s3+1,len(s)-s3-4), *
from (
select *, s2 = len(s) - CHARINDEX(' ',REVERSE(s),LEN(s)-s3+1)
from (
select s, s1 = charindex(' ',s), s3 = len(s) - CHARINDEX(' ',reverse(s))
from @s
) a
) a
) a

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2011-10-31 : 13:56:02
you can make use SUBSTRING and PATINDEX functions to get the required parts from the string

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

pakwichek
Starting Member

12 Posts
Posted - 2011-10-31 : 23:23:28
quote:
Originally posted by nigelrivett

declare @s table (s varchar(1000), id int identity)
insert @s select '5435 Akwa Joseph Ndifon 200k 34514.pdf'
insert @s select '5436 Njang 250k 34516.pdf'

select CustomerID, CustomerName, [Amount Paid] = case when [Amount Paid] like '%k' then CONVERT(int,replace([Amount Paid],'k','')) * 1000 else CONVERT(int,[Amount Paid]) end, ReceiptNumber
from (
select CustomerID = SUBSTRING(s,1,s1),
CustomerName = SUBSTRING(s,s1+1,s2-s1),
[Amount Paid]= SUBSTRING(s,s2+1,s3-s2),
ReceiptNumber = SUBSTRING(s,s3+1,len(s)-s3-4), *
from (
select *, s2 = len(s) - CHARINDEX(' ',REVERSE(s),LEN(s)-s3+1)
from (
select s, s1 = charindex(' ',s), s3 = len(s) - CHARINDEX(' ',reverse(s))
from @s
) a
) a
) a

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.


Hello Sir

That works. This was really excellent. I thank you a lot
How to modify same query to update two fields in the same table? The fields are Amount and ReceiptNumber

Thanks

peter Akwichek
Go to Top of Page

jassi.singh
Posting Yak Master

122 Posts
Posted - 2011-11-01 : 04:31:02
quote:
Originally posted by pakwichek

quote:
Originally posted by nigelrivett

declare @s table (s varchar(1000), id int identity)
insert @s select '5435 Akwa Joseph Ndifon 200k 34514.pdf'
insert @s select '5436 Njang 250k 34516.pdf'

select CustomerID, CustomerName, [Amount Paid] = case when [Amount Paid] like '%k' then CONVERT(int,replace([Amount Paid],'k','')) * 1000 else CONVERT(int,[Amount Paid]) end, ReceiptNumber
from (
select CustomerID = SUBSTRING(s,1,s1),
CustomerName = SUBSTRING(s,s1+1,s2-s1),
[Amount Paid]= SUBSTRING(s,s2+1,s3-s2),
ReceiptNumber = SUBSTRING(s,s3+1,len(s)-s3-4), *
from (
select *, s2 = len(s) - CHARINDEX(' ',REVERSE(s),LEN(s)-s3+1)
from (
select s, s1 = charindex(' ',s), s3 = len(s) - CHARINDEX(' ',reverse(s))
from @s
) a
) a
) a

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.


Hello Sir

That works. This was really excellent. I thank you a lot
How to modify same query to update two fields in the same table? The fields are Amount and ReceiptNumber

Thanks

peter Akwichek


---------------------------------
You can store your result in temp table and them you can do something like below

update tablename set field1=new value from tablename inner join temptablename



Please mark answer as accepted if it helped you.

Thanks,
Jassi Singh
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2011-11-01 : 04:45:11
quote:
Originally posted by pakwichek

quote:
Originally posted by nigelrivett

declare @s table (s varchar(1000), id int identity)
insert @s select '5435 Akwa Joseph Ndifon 200k 34514.pdf'
insert @s select '5436 Njang 250k 34516.pdf'

select CustomerID, CustomerName, [Amount Paid] = case when [Amount Paid] like '%k' then CONVERT(int,replace([Amount Paid],'k','')) * 1000 else CONVERT(int,[Amount Paid]) end, ReceiptNumber
from (
select CustomerID = SUBSTRING(s,1,s1),
CustomerName = SUBSTRING(s,s1+1,s2-s1),
[Amount Paid]= SUBSTRING(s,s2+1,s3-s2),
ReceiptNumber = SUBSTRING(s,s3+1,len(s)-s3-4), *
from (
select *, s2 = len(s) - CHARINDEX(' ',REVERSE(s),LEN(s)-s3+1)
from (
select s, s1 = charindex(' ',s), s3 = len(s) - CHARINDEX(' ',reverse(s))
from @s
) a
) a
) a

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.


Hello Sir

That works. This was really excellent. I thank you a lot
How to modify same query to update two fields in the same table? The fields are Amount and ReceiptNumber

Thanks

peter Akwichek

change select to an update


update a
set Amount = case when [Amount Paid] like '%k' then CONVERT(int,replace([Amount Paid],'k','')) * 1000 else CONVERT(int,[Amount Paid]) end, ReceiptNumber=ReceiptNumber
from (
select Amount ,ReceiptNumber
,CustomerID = SUBSTRING(s,1,s1),
CustomerName = SUBSTRING(s,s1+1,s2-s1), 
[Amount Paid]= SUBSTRING(s,s2+1,s3-s2), 
ReceiptNo = SUBSTRING(s,s3+1,len(s)-s3-4), *
from (
select *, s2 = len(s) - CHARINDEX(' ',REVERSE(s),LEN(s)-s3+1)
from (
select *,s, s1 = charindex(' ',s), s3 = len(s) - CHARINDEX(' ',reverse(s))
from @s
) a
) a
) a




------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2011-11-01 : 04:55:30
quote:
Originally posted by jassi.singh

quote:
Originally posted by pakwichek

quote:
Originally posted by nigelrivett

declare @s table (s varchar(1000), id int identity)
insert @s select '5435 Akwa Joseph Ndifon 200k 34514.pdf'
insert @s select '5436 Njang 250k 34516.pdf'

select CustomerID, CustomerName, [Amount Paid] = case when [Amount Paid] like '%k' then CONVERT(int,replace([Amount Paid],'k','')) * 1000 else CONVERT(int,[Amount Paid]) end, ReceiptNumber
from (
select CustomerID = SUBSTRING(s,1,s1),
CustomerName = SUBSTRING(s,s1+1,s2-s1),
[Amount Paid]= SUBSTRING(s,s2+1,s3-s2),
ReceiptNumber = SUBSTRING(s,s3+1,len(s)-s3-4), *
from (
select *, s2 = len(s) - CHARINDEX(' ',REVERSE(s),LEN(s)-s3+1)
from (
select s, s1 = charindex(' ',s), s3 = len(s) - CHARINDEX(' ',reverse(s))
from @s
) a
) a
) a

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.


Hello Sir

That works. This was really excellent. I thank you a lot
How to modify same query to update two fields in the same table? The fields are Amount and ReceiptNumber

Thanks

peter Akwichek


---------------------------------
You can store your result in temp table and them you can do something like below

update tablename set field1=new value from tablename inner join temptablename



Please mark answer as accepted if it helped you.

Thanks,
Jassi Singh

no need of temporary table
see my suggestion
you can wrap them inline in update

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

pakwichek
Starting Member

12 Posts
Posted - 2011-11-01 : 08:31:10
quote:
Originally posted by visakh16

quote:
Originally posted by pakwichek

quote:
Originally posted by nigelrivett

declare @s table (s varchar(1000), id int identity)
insert @s select '5435 Akwa Joseph Ndifon 200k 34514.pdf'
insert @s select '5436 Njang 250k 34516.pdf'

select CustomerID, CustomerName, [Amount Paid] = case when [Amount Paid] like '%k' then CONVERT(int,replace([Amount Paid],'k','')) * 1000 else CONVERT(int,[Amount Paid]) end, ReceiptNumber
from (
select CustomerID = SUBSTRING(s,1,s1),
CustomerName = SUBSTRING(s,s1+1,s2-s1),
[Amount Paid]= SUBSTRING(s,s2+1,s3-s2),
ReceiptNumber = SUBSTRING(s,s3+1,len(s)-s3-4), *
from (
select *, s2 = len(s) - CHARINDEX(' ',REVERSE(s),LEN(s)-s3+1)
from (
select s, s1 = charindex(' ',s), s3 = len(s) - CHARINDEX(' ',reverse(s))
from @s
) a
) a
) a

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.


Hello Sir

That works. This was really excellent. I thank you a lot
How to modify same query to update two fields in the same table? The fields are Amount and ReceiptNumber

Thanks

peter Akwichek

change select to an update


update a
set Amount = case when [Amount Paid] like '%k' then CONVERT(int,replace([Amount Paid],'k','')) * 1000 else CONVERT(int,[Amount Paid]) end, ReceiptNumber=ReceiptNumber
from (
select Amount ,ReceiptNumber
,CustomerID = SUBSTRING(s,1,s1),
CustomerName = SUBSTRING(s,s1+1,s2-s1), 
[Amount Paid]= SUBSTRING(s,s2+1,s3-s2), 
ReceiptNo = SUBSTRING(s,s3+1,len(s)-s3-4), *
from (
select *, s2 = len(s) - CHARINDEX(' ',REVERSE(s),LEN(s)-s3+1)
from (
select *,s, s1 = charindex(' ',s), s3 = len(s) - CHARINDEX(' ',reverse(s))
from @s
) a
) a
) a




------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/




Here is the Table Structure I am using to do this update.

ID int
CUSTOMERID int
FileName varchar(200)
FileType varchar(30)
CustomerName Varchar(60)
TITReceipt int
Amount int


I updated the query based on the above table structure as below

-----
update a
set Amount = case when [Amount] like '%k' then CONVERT(int,replace([Amount],'k','')) * 1000 else CONVERT(int,[Amount]) end,
TITReceipt=TITReceipt,
CustomerName= CustomerName
from (
select Amount ,TITReceipt
,CustomerID = SUBSTRING([FileName] ,1,s1),
CustomerName = SUBSTRING([FileName] ,s1+1,s2-s1),
[Amount]= SUBSTRING([FileName] ,s2+1,s3-s2),
TITReceipt = SUBSTRING([FileName] ,s3+1,len(s)-s3-4), *
from (
select *, s2 = len([FileName] ) - CHARINDEX(' ',REVERSE([FileName] ),LEN([FileName] )-s3+1)
from (
select *,[FileName] , s1 = charindex(' ',[FileName] ), s3 = len([FileName] ) - CHARINDEX(' ',reverse([FileName] ))
from STUDENT_DIR_INFOR
) a
) a
) a
WHERE FileType='BANK RECEIPT'

--
--But When I execute the above, I get the following error
"Server: Msg 8156, Level 16, State 1, Line 1
The column 'FileName' was specified multiple times for 'a'."
Can't quite figure this error out, I am sure, one more correction we are done, I really appreciate all your help

thanks








peter Akwichek
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2011-11-01 : 11:43:39
[code]
update a
set Amount = case when [AmountVal] like '%k' then CONVERT(int,replace([AmountVal],'k','')) * 1000 else CONVERT(int,[AmountVal]) end,
TITReceipt=TITReceiptVal,
CustomerName= CustomerNameVal
from (
select Amount ,TITReceipt
,CustomerID = SUBSTRING([FileName] ,1,s1),
CustomerName ,
CustomerNameVal = SUBSTRING([FileName] ,s1+1,s2-s1),
[AmountVal]= SUBSTRING([FileName] ,s2+1,s3-s2),
TITReceipt,FileType,
TITReceiptVal = SUBSTRING([FileName] ,s3+1,len(s)-s3-4), *
from (
select *, s2 = len([FileName] ) - CHARINDEX(' ',REVERSE([FileName] ),LEN([FileName] )-s3+1)
from (
select *, s1 = charindex(' ',[FileName] ), s3 = len([FileName] ) - CHARINDEX(' ',reverse([FileName] ))
from STUDENT_DIR_INFOR
) a
) a
) a
WHERE FileType='BANK RECEIPT'
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts
Posted - 2011-11-01 : 13:05:40
This gets rid of the s error but I still don't think it will work as you need to update the table rather than the derived info here.
I think it needs to join to STUDENT_DIR_INFOR


update STUDENT_DIR_INFOR
set Amount = case when [AmountVal] like '%k' then CONVERT(int,replace([AmountVal],'k','')) * 1000 else CONVERT(int,[AmountVal]) end,
TITReceipt=TITReceiptVal,
CustomerName= CustomerNameVal
from (
select Amount ,TITReceipt
,CustomerID = SUBSTRING([FileName] ,1,s1),
CustomerName ,
CustomerNameVal = SUBSTRING([FileName] ,s1+1,s2-s1),
[AmountVal]= SUBSTRING([FileName] ,s2+1,s3-s2),
TITReceipt,FileType,
TITReceiptVal = SUBSTRING([FileName] ,s3+1,len([FileName])-s3-4), *
from (
select *, s2 = len([FileName] ) - CHARINDEX(' ',REVERSE([FileName] ),LEN([FileName] )-s3+1)
from (
select *, s1 = charindex(' ',[FileName] ), s3 = len([FileName] ) - CHARINDEX(' ',reverse([FileName] ))
from STUDENT_DIR_INFOR
) a
) a
) a
WHERE FileType='BANK RECEIPT'


==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

pakwichek
Starting Member

12 Posts
Posted - 2011-11-01 : 13:16:04
quote:
Originally posted by nigelrivett

This gets rid of the s error but I still don't think it will work as you need to update the table rather than the derived info here.
I think it needs to join to STUDENT_DIR_INFOR


update STUDENT_DIR_INFOR
set Amount = case when [AmountVal] like '%k' then CONVERT(int,replace([AmountVal],'k','')) * 1000 else CONVERT(int,[AmountVal]) end,
TITReceipt=TITReceiptVal,
CustomerName= CustomerNameVal
from (
select Amount ,TITReceipt
,CustomerID = SUBSTRING([FileName] ,1,s1),
CustomerName ,
CustomerNameVal = SUBSTRING([FileName] ,s1+1,s2-s1),
[AmountVal]= SUBSTRING([FileName] ,s2+1,s3-s2),
TITReceipt,FileType,
TITReceiptVal = SUBSTRING([FileName] ,s3+1,len([FileName])-s3-4), *
from (
select *, s2 = len([FileName] ) - CHARINDEX(' ',REVERSE([FileName] ),LEN([FileName] )-s3+1)
from (
select *, s1 = charindex(' ',[FileName] ), s3 = len([FileName] ) - CHARINDEX(' ',reverse([FileName] ))
from STUDENT_DIR_INFOR
) a
) a
) a
WHERE FileType='BANK RECEIPT'


==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.


OK, SIr, I executed the script but has another error below

"Server: Msg 207, Level 16, State 3, Line 31
Invalid column name 's'.
Server: Msg 8156, Level 16, State 1, Line 31
The column 'Amount' was specified multiple times for 'a'."



Here is more information with table and data to help with error fixing:
-----------

DECLARE @Student_Dir_Infor Table (
ID int identity,
CUSTOMERID int,
FileName varchar(200),
FileType varchar(30),
CustomerName Varchar(60),
TITReceipt int,
Amount int
)

INSERT INTO @Student_Dir_Infor (CUSTOMERID,FileName,FileType)
SELECT '5261', '5261 ROkara 15k 3996.pdf', 'BANK RECEIPT'
INSERT INTO @Student_Dir_Infor (CUSTOMERID,FileName,FileType)
SELECT '5266', '5266 MNdifon 15k 10887.pdf' ,'BANK RECEIPT'
INSERT INTO @Student_Dir_Infor (CUSTOMERID,FileName,FileType)
SELECT '5267', '5267 ADoh 15k 10886.pdf' ,'BANK RECEIPT'
INSERT INTO @Student_Dir_Infor (CUSTOMERID,FileName,FileType)
SELECT '5268', '5268 MMbonda 200k 10888.pdf' ,'BANK RECEIPT'
INSERT INTO @Student_Dir_Infor (CUSTOMERID,FileName,FileType)
SELECT '5269', '5269 MOkpara 100k 10889.pdf' ,'BANK RECEIPT'










update a
set Amount = case when [AmountVal] like '%k' then CONVERT(int,replace([AmountVal],'k','')) * 1000 else CONVERT(int,[AmountVal]) end,
TITReceipt=TITReceiptVal,
CustomerName= CustomerNameVal
from (
select Amount ,TITReceipt
,CustomerID = SUBSTRING([FileName] ,1,s1),
CustomerName ,
CustomerNameVal = SUBSTRING([FileName] ,s1+1,s2-s1),
[AmountVal]= SUBSTRING([FileName] ,s2+1,s3-s2),
TITReceipt,FileType,
TITReceiptVal = SUBSTRING([FileName] ,s3+1,len(s)-s3-4), *
from (
select *, s2 = len([FileName] ) - CHARINDEX(' ',REVERSE([FileName] ),LEN([FileName] )-s3+1)
from (
select *, s1 = charindex(' ',[FileName] ), s3 = len([FileName] ) - CHARINDEX(' ',reverse([FileName] ))
from @STUDENT_DIR_INFOR
) a
) a
) a
WHERE FileType='BANK RECEIPT'










peter Akwichek
Go to Top of Page

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts
Posted - 2011-11-01 : 13:28:37
A number of issue fixed
DECLARE @Student_Dir_Infor Table (
ID int identity,
CUSTOMERID int,
FileName varchar(200),
FileType varchar(30),
CustomerName Varchar(60),
TITReceipt int,
Amount int
)

INSERT INTO @Student_Dir_Infor (CUSTOMERID,FileName,FileType)
SELECT '5261', '5261 ROkara 15k 3996.pdf', 'BANK RECEIPT'
INSERT INTO @Student_Dir_Infor (CUSTOMERID,FileName,FileType)
SELECT '5266', '5266 MNdifon 15k 10887.pdf' ,'BANK RECEIPT'
INSERT INTO @Student_Dir_Infor (CUSTOMERID,FileName,FileType)
SELECT '5267', '5267 ADoh 15k 10886.pdf' ,'BANK RECEIPT'
INSERT INTO @Student_Dir_Infor (CUSTOMERID,FileName,FileType)
SELECT '5268', '5268 MMbonda 200k 10888.pdf' ,'BANK RECEIPT'
INSERT INTO @Student_Dir_Infor (CUSTOMERID,FileName,FileType)
SELECT '5269', '5269 MOkpara 100k 10889.pdf' ,'BANK RECEIPT'

update a
set Amount = case when [AmountVal] like '%k' then CONVERT(int,replace([AmountVal],'k','')) * 1000 else CONVERT(int,[AmountVal]) end,
TITReceipt=TITReceiptVal,
CustomerName= CustomerNameVal
from (
select Amount
,CustomerID = SUBSTRING([FileName] ,1,s1)
,CustomerName
,CustomerNameVal = SUBSTRING([FileName] ,s1+1,s2-s1)
,[AmountVal]= SUBSTRING([FileName] ,s2+1,s3-s2),
TITReceipt,FileType,
TITReceiptVal = SUBSTRING([FileName] ,s3+1,len([FileName])-s3-4)
from (
select *, s2 = len([FileName] ) - CHARINDEX(' ',REVERSE([FileName] ),LEN([FileName] )-s3+1)
from (
select *, s1 = charindex(' ',[FileName] ), s3 = len([FileName] ) - CHARINDEX(' ',reverse([FileName] ))
from @STUDENT_DIR_INFOR
) a
) a
) a
WHERE FileType='BANK RECEIPT'

select * from @Student_Dir_Infor

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2011-11-01 : 13:30:15
[code]
DECLARE @Student_Dir_Infor Table (
ID int identity,
CUSTOMERID int,
FileName varchar(200),
FileType varchar(30),
CustomerName Varchar(60),
TITReceipt int,
Amount int
)

INSERT INTO @Student_Dir_Infor (CUSTOMERID,FileName,FileType)
SELECT '5261', '5261 ROkara 15k 3996.pdf', 'BANK RECEIPT'
INSERT INTO @Student_Dir_Infor (CUSTOMERID,FileName,FileType)
SELECT '5266', '5266 MNdifon 15k 10887.pdf' ,'BANK RECEIPT'
INSERT INTO @Student_Dir_Infor (CUSTOMERID,FileName,FileType)
SELECT '5267', '5267 ADoh 15k 10886.pdf' ,'BANK RECEIPT'
INSERT INTO @Student_Dir_Infor (CUSTOMERID,FileName,FileType)
SELECT '5268', '5268 MMbonda 200k 10888.pdf' ,'BANK RECEIPT'
INSERT INTO @Student_Dir_Infor (CUSTOMERID,FileName,FileType)
SELECT '5269', '5269 MOkpara 100k 10889.pdf' ,'BANK RECEIPT'










update a
set Amount = case when [AmountVal] like '%k' then CONVERT(int,replace([AmountVal],'k','')) * 1000 else CONVERT(int,[AmountVal]) end,
TITReceipt=TITReceiptVal,
CustomerName= CustomerNameVal
from (
select CustomerIDVal = SUBSTRING([FileName] ,1,s1),
CustomerNameVal = SUBSTRING([FileName] ,s1+1,s2-s1),
[AmountVal]= SUBSTRING([FileName] ,s2+1,s3-s2),
TITReceiptVal = SUBSTRING([FileName] ,s3+1,len([FileName])-s3-4), *
from (
select *, s2 = len([FileName] ) - CHARINDEX(' ',REVERSE([FileName] ),LEN([FileName] )-s3+1)
from (
select *, s1 = charindex(' ',[FileName] ), s3 = len([FileName] ) - CHARINDEX(' ',reverse([FileName] ))
from @STUDENT_DIR_INFOR
) a
) a
) a
WHERE FileType='BANK RECEIPT'

select * from @Student_Dir_Infor

output
------------------------------------------
ID CUSTOMERID FileName FileType CustomerName TITReceipt Amount
1 5261 5261 ROkara 15k 3996.pdf BANK RECEIPT ROkara 3996 15000
2 5266 5266 MNdifon 15k 10887.pdf BANK RECEIPT MNdifon 10887 15000
3 5267 5267 ADoh 15k 10886.pdf BANK RECEIPT ADoh 10886 15000
4 5268 5268 MMbonda 200k 10888.pdf BANK RECEIPT MMbonda 10888 200000
5 5269 5269 MOkpara 100k 10889.pdf BANK RECEIPT MOkpara 10889 100000

[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

pakwichek
Starting Member

12 Posts
Posted - 2011-11-01 : 13:50:51
quote:
Originally posted by visakh16


DECLARE @Student_Dir_Infor Table (
ID int identity,
CUSTOMERID int,
FileName varchar(200),
FileType varchar(30),
CustomerName Varchar(60),
TITReceipt int,
Amount int
)

INSERT INTO @Student_Dir_Infor (CUSTOMERID,FileName,FileType)
SELECT	'5261',	'5261 ROkara 15k 3996.pdf',	'BANK RECEIPT'	
INSERT INTO @Student_Dir_Infor (CUSTOMERID,FileName,FileType)
SELECT	'5266', '5266 MNdifon 15k 10887.pdf'	,'BANK RECEIPT'
INSERT INTO @Student_Dir_Infor (CUSTOMERID,FileName,FileType)
SELECT	'5267',	'5267 ADoh 15k 10886.pdf'	,'BANK RECEIPT'
INSERT INTO @Student_Dir_Infor (CUSTOMERID,FileName,FileType)
SELECT	'5268',	'5268 MMbonda 200k 10888.pdf'	,'BANK RECEIPT'	
INSERT INTO @Student_Dir_Infor (CUSTOMERID,FileName,FileType)
SELECT '5269',	'5269 MOkpara 100k 10889.pdf'	,'BANK RECEIPT'










update a
set Amount = case when [AmountVal] like '%k' then CONVERT(int,replace([AmountVal],'k','')) * 1000 else CONVERT(int,[AmountVal]) end, 
TITReceipt=TITReceiptVal,
CustomerName= CustomerNameVal 
from (
select CustomerIDVal = SUBSTRING([FileName] ,1,s1),
CustomerNameVal = SUBSTRING([FileName] ,s1+1,s2-s1), 
[AmountVal]= SUBSTRING([FileName] ,s2+1,s3-s2), 
TITReceiptVal = SUBSTRING([FileName] ,s3+1,len([FileName])-s3-4), *
from (
select *, s2 = len([FileName] ) - CHARINDEX(' ',REVERSE([FileName] ),LEN([FileName] )-s3+1)
from (
select *, s1 = charindex(' ',[FileName] ), s3 = len([FileName] ) - CHARINDEX(' ',reverse([FileName] ))
from @STUDENT_DIR_INFOR
) a
) a
) a
WHERE FileType='BANK RECEIPT'

select * from  @Student_Dir_Infor

output
------------------------------------------
ID	CUSTOMERID	FileName	        FileType	CustomerName	TITReceipt	Amount
1	5261	5261 ROkara 15k 3996.pdf	BANK RECEIPT	ROkara	        3996	15000
2	5266	5266 MNdifon 15k 10887.pdf	BANK RECEIPT	MNdifon	        10887	15000
3	5267	5267 ADoh 15k 10886.pdf	        BANK RECEIPT	ADoh	        10886	15000
4	5268	5268 MMbonda 200k 10888.pdf	BANK RECEIPT	MMbonda	        10888	200000
5	5269	5269 MOkpara 100k 10889.pdf	BANK RECEIPT	MOkpara	        10889	100000



------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/




Oh No, what did I do wrong. I can't get the same result posted

I get this error
Server: Msg 4421, Level 16, State 1, Line 31
Derived table 'a' is not updatable because a column of the derived table is derived or constant.


I also try running it by exactly and changing change to the true underline sql table,, same error. I hope I did not copy wrong code accidentally, I am so sorry,.. Looks like it should work!!





peter Akwichek
Go to Top of Page

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts
Posted - 2011-11-01 : 13:51:36
Try my version.
What version of sql server are you using - you might be back to my earlier suggestion.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts
Posted - 2011-11-01 : 13:59:07
Try this one
and I've just amended it to add all of the columns to the join.

DECLARE @Student_Dir_Infor Table (
ID int identity,
CUSTOMERID int,
FileName varchar(200),
FileType varchar(30),
CustomerName Varchar(60),
TITReceipt int,
Amount int
)

INSERT INTO @Student_Dir_Infor (CUSTOMERID,FileName,FileType)
SELECT '5261', '5261 ROkara 15k 3996.pdf', 'BANK RECEIPT'
INSERT INTO @Student_Dir_Infor (CUSTOMERID,FileName,FileType)
SELECT '5266', '5266 MNdifon 15k 10887.pdf' ,'BANK RECEIPT'
INSERT INTO @Student_Dir_Infor (CUSTOMERID,FileName,FileType)
SELECT '5267', '5267 ADoh 15k 10886.pdf' ,'BANK RECEIPT'
INSERT INTO @Student_Dir_Infor (CUSTOMERID,FileName,FileType)
SELECT '5268', '5268 MMbonda 200k 10888.pdf' ,'BANK RECEIPT'
INSERT INTO @Student_Dir_Infor (CUSTOMERID,FileName,FileType)
SELECT '5269', '5269 MOkpara 100k 10889.pdf' ,'BANK RECEIPT'

update @Student_Dir_Infor
set Amount = case when a.[AmountVal] like '%k' then CONVERT(int,replace(a.[AmountVal],'k','')) * 1000 else CONVERT(int,a.[AmountVal]) end,
TITReceipt=a.TITReceiptVal,
CustomerName= a.CustomerNameVal
from (
select Amount
,CustomerID = SUBSTRING([FileName] ,1,s1)
,CustomerName
,CustomerNameVal = SUBSTRING([FileName] ,s1+1,s2-s1)
,[AmountVal]= SUBSTRING([FileName] ,s2+1,s3-s2),
TITReceipt,FileType,FileName ,
TITReceiptVal = SUBSTRING([FileName] ,s3+1,len([FileName])-s3-4)
from (
select *, s2 = len([FileName] ) - CHARINDEX(' ',REVERSE([FileName] ),LEN([FileName] )-s3+1)
from (
select *, s1 = charindex(' ',[FileName] ), s3 = len([FileName] ) - CHARINDEX(' ',reverse([FileName] ))
from @STUDENT_DIR_INFOR
) a
) a
) a
join @Student_Dir_Infor sdi
on a.CUSTOMERID = sdi.CUSTOMERID
WHERE sdi.FileType='BANK RECEIPT'
and sdi.FileName = a.FileName
and sdi.FileType = a.FileType

select * from @Student_Dir_Infor

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

pakwichek
Starting Member

12 Posts
Posted - 2011-11-01 : 13:59:57
quote:
Originally posted by nigelrivett

Try my version.
What version of sql server are you using - you might be back to my earlier suggestion.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.


I have SQL 2000


peter Akwichek
Go to Top of Page

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts
Posted - 2011-11-01 : 14:02:07
Could be the issue - try that last one I posted.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

pakwichek
Starting Member

12 Posts
Posted - 2011-11-01 : 14:11:05
quote:
Originally posted by nigelrivett

Try this one
and I've just amended it to add all of the columns to the join.

DECLARE @Student_Dir_Infor Table (
ID int identity,
CUSTOMERID int,
FileName varchar(200),
FileType varchar(30),
CustomerName Varchar(60),
TITReceipt int,
Amount int
)

INSERT INTO @Student_Dir_Infor (CUSTOMERID,FileName,FileType)
SELECT '5261', '5261 ROkara 15k 3996.pdf', 'BANK RECEIPT'
INSERT INTO @Student_Dir_Infor (CUSTOMERID,FileName,FileType)
SELECT '5266', '5266 MNdifon 15k 10887.pdf' ,'BANK RECEIPT'
INSERT INTO @Student_Dir_Infor (CUSTOMERID,FileName,FileType)
SELECT '5267', '5267 ADoh 15k 10886.pdf' ,'BANK RECEIPT'
INSERT INTO @Student_Dir_Infor (CUSTOMERID,FileName,FileType)
SELECT '5268', '5268 MMbonda 200k 10888.pdf' ,'BANK RECEIPT'
INSERT INTO @Student_Dir_Infor (CUSTOMERID,FileName,FileType)
SELECT '5269', '5269 MOkpara 100k 10889.pdf' ,'BANK RECEIPT'

update @Student_Dir_Infor
set Amount = case when a.[AmountVal] like '%k' then CONVERT(int,replace(a.[AmountVal],'k','')) * 1000 else CONVERT(int,a.[AmountVal]) end,
TITReceipt=a.TITReceiptVal,
CustomerName= a.CustomerNameVal
from (
select Amount
,CustomerID = SUBSTRING([FileName] ,1,s1)
,CustomerName
,CustomerNameVal = SUBSTRING([FileName] ,s1+1,s2-s1)
,[AmountVal]= SUBSTRING([FileName] ,s2+1,s3-s2),
TITReceipt,FileType,FileName ,
TITReceiptVal = SUBSTRING([FileName] ,s3+1,len([FileName])-s3-4)
from (
select *, s2 = len([FileName] ) - CHARINDEX(' ',REVERSE([FileName] ),LEN([FileName] )-s3+1)
from (
select *, s1 = charindex(' ',[FileName] ), s3 = len([FileName] ) - CHARINDEX(' ',reverse([FileName] ))
from @STUDENT_DIR_INFOR
) a
) a
) a
join @Student_Dir_Infor sdi
on a.CUSTOMERID = sdi.CUSTOMERID
WHERE sdi.FileType='BANK RECEIPT'
and sdi.FileName = a.FileName
and sdi.FileType = a.FileType

select * from @Student_Dir_Infor

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.




COOOL COOL!! THE JOIN DID THE TRICK!! BRAVO!!
THANKS A MILLIONS!! WORKS LIKE A CHARM



peter Akwichek
Go to Top of Page

pakwichek
Starting Member

12 Posts
Posted - 2011-11-01 : 14:15:20
quote:
Originally posted by visakh16


DECLARE @Student_Dir_Infor Table (
ID int identity,
CUSTOMERID int,
FileName varchar(200),
FileType varchar(30),
CustomerName Varchar(60),
TITReceipt int,
Amount int
)

INSERT INTO @Student_Dir_Infor (CUSTOMERID,FileName,FileType)
SELECT	'5261',	'5261 ROkara 15k 3996.pdf',	'BANK RECEIPT'	
INSERT INTO @Student_Dir_Infor (CUSTOMERID,FileName,FileType)
SELECT	'5266', '5266 MNdifon 15k 10887.pdf'	,'BANK RECEIPT'
INSERT INTO @Student_Dir_Infor (CUSTOMERID,FileName,FileType)
SELECT	'5267',	'5267 ADoh 15k 10886.pdf'	,'BANK RECEIPT'
INSERT INTO @Student_Dir_Infor (CUSTOMERID,FileName,FileType)
SELECT	'5268',	'5268 MMbonda 200k 10888.pdf'	,'BANK RECEIPT'	
INSERT INTO @Student_Dir_Infor (CUSTOMERID,FileName,FileType)
SELECT '5269',	'5269 MOkpara 100k 10889.pdf'	,'BANK RECEIPT'










update a
set Amount = case when [AmountVal] like '%k' then CONVERT(int,replace([AmountVal],'k','')) * 1000 else CONVERT(int,[AmountVal]) end, 
TITReceipt=TITReceiptVal,
CustomerName= CustomerNameVal 
from (
select CustomerIDVal = SUBSTRING([FileName] ,1,s1),
CustomerNameVal = SUBSTRING([FileName] ,s1+1,s2-s1), 
[AmountVal]= SUBSTRING([FileName] ,s2+1,s3-s2), 
TITReceiptVal = SUBSTRING([FileName] ,s3+1,len([FileName])-s3-4), *
from (
select *, s2 = len([FileName] ) - CHARINDEX(' ',REVERSE([FileName] ),LEN([FileName] )-s3+1)
from (
select *, s1 = charindex(' ',[FileName] ), s3 = len([FileName] ) - CHARINDEX(' ',reverse([FileName] ))
from @STUDENT_DIR_INFOR
) a
) a
) a
WHERE FileType='BANK RECEIPT'

select * from  @Student_Dir_Infor

output
------------------------------------------
ID	CUSTOMERID	FileName	        FileType	CustomerName	TITReceipt	Amount
1	5261	5261 ROkara 15k 3996.pdf	BANK RECEIPT	ROkara	        3996	15000
2	5266	5266 MNdifon 15k 10887.pdf	BANK RECEIPT	MNdifon	        10887	15000
3	5267	5267 ADoh 15k 10886.pdf	        BANK RECEIPT	ADoh	        10886	15000
4	5268	5268 MMbonda 200k 10888.pdf	BANK RECEIPT	MMbonda	        10888	200000
5	5269	5269 MOkpara 100k 10889.pdf	BANK RECEIPT	MOkpara	        10889	100000



------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/






: THANKS A LOT FOR ALL HELP WITH THIS



peter Akwichek
Go to Top of Page

pakwichek
Starting Member

12 Posts
Posted - 2011-11-01 : 15:30:51
quote:
Originally posted by nigelrivett

Could be the issue - try that last one I posted.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.



Hello Sir,

Now that everything works, how to ignore rows that have data conversion errors! I will like to update everything and ignore all errors

Thanks a lot for any tip or code snippet






peter Akwichek
Go to Top of Page
    Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site