Remove Issuance and Expiry dates
-
Hi everyone,
I have a sql table in which data looks like this:
I have to modify two columns (natid and passport) data so that it would only show the actualy natid and passport numbers.
For e.g natid and passport of ABC1 would show ID-123 and PASS-456 when using select query.
issuance and expiry dates should be removed. How can I modify my table?
Here is the code:
drop table if exists info;
create table info(id int identity(1,1),names nvarchar(100),natid nvarchar(100),passport nvarchar(100));
insert into info values
('ABC1','ID-123 issued 14 Mar 2006 expires 14 Dec 2006','PASS-456 issued 2006 expires Mar 2011'),
('DEF2','ID-123 expires 02 Oct 2030','PASS-456 issued 14 Mar 2006 expires 14 Mar 2011'),
('GHI3','ID-123 issued 18 Nov 2001 expires 30 Mar 2025','PASS-456 issued 14 Mar 2006 '),
('JKL4','ID-123 issued 29 Apr 1994 expires 29 Sep 2024','PASS-456 issued 14 Mar 2006 expires 14 Mar 2011'),
('MNO5','ID-123 issued 01 Feb 1980 ','PASS-456 issued 14 Mar 2006 expires 2011')
select * from info;- This topic was modified 2 years ago by Jobs90312.
-
if the initial string has no spaces you can use a combination of charindex and substring - you should be able to built something with these 2 functions without the need for us here to give the code for it.
-
By removing this data, are you destroying data that may need to be preserved? The data you want to remove looks like notes of some sort.
While you may want to remove it from those columns, should you be preserving it someplace else?
This is a classic example of why you should size the data types in a column properly. Because there was room to store something in there, the users did.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply