character trim help - SQL Server Forums

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

SQL Server 2008 Forums

Transact-SQL (2008)

character trim help

Author

Topic

ssdeveloper
Starting Member

37 Posts

Posted - 2011-08-22 : 16:56:59

Hi

I have a column and which has entries like '06-01-10-', '06-01-10-',
etc,. But some of the entries were like this: '12-10-08 NO FORMER-','06-01-10efffective-'. Can I trim the 'effective' and 'NO FORMER' parts of the entries, I mean the extended text after the date and can it be dynamic. Any help regarding this Highly appreciated. Thank you so much in advance.

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2011-08-22 : 17:02:12

yes that s possible. but what you need to look into is why is it like that in the first place and tackle that which is the underlying bigger problem . can you explain to us why all those texts are tacked on to what I assume are date fields?

If you don't have the passion to help people, you have no passion

ssdeveloper
Starting Member

37 Posts

Posted - 2011-08-22 : 17:09:52

Hi

Actually we derived that datefields column from already existing column whose entries for example read as : 'IND_CHE_RES_12_10_08 NO FORMER' So some of these have the texts after date which kind of where I stuck to trim that. I know its a mess done by somebody else but trying to fix that now.
Can any one give me a solution or guide me in right direction.
Thanks.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-08-22 : 17:19:23

If the data is always consistant MM-DD-YY followed by possible garbage you could use the LEFT function:

DECLARE @Foo TABLE (Val VARCHAR(50))

INSERT @Foo (Val)
VALUES ('12-10-08 NO FORMER-'),('06-01-10efffective-')

SELECT LEFT(Val, 8) AS NewVal
FROM @Foo

ssdeveloper
Starting Member

37 Posts

Posted - 2011-08-22 : 17:39:28

Thanks L,

But what if its not consistent? I have some in mm-dd-yyyy, I know this is crap but....
Thats the reason I asked if dynamic solution is possible, meaning -- say figure of character string after the numeric kind of thing!

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-08-22 : 17:50:35

Hard to say. may a CASE expression with a LIKE comparison?

DECLARE @Foo TABLE (Val VARCHAR(50))

INSERT @Foo (Val)
VALUES 
    ('12-10-08 NO FORMER-'),
    ('06-01-10efffective-'),
    ('12-10-2011 NO FORMER-'),
    ('06-01-2009efffective-'),
    ('06-01-efffective-')


SELECT
    CASE 
        WHEN Val LIKE '[0-9][0-9]-[0-9][0-9]-[0-9][0-9][0-9][0-9]%' THEN LEFT(Val, 10)
        WHEN Val LIKE '[0-9][0-9]-[0-9][0-9]-[0-9][0-9]%' THEN LEFT(Val, 8)
        ELSE NULL
    END AS NewVal
FROM @Foo

If you have more specific data samples you can provide that might help too.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources