SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Remove first four characters
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

astralis
Yak Posting Veteran

USA
59 Posts

Posted - 02/27/2014 :  20:51:54  Show Profile  Reply with Quote
I have a MSSQL Server 2008 column with rows that look like this:

99991002
1001
99991000
999
9999998
997
996
9999995
9999994
993
9999992
991

I need to capture the rows that include the first four digits (9999) and then return only the remain digits of that row.

For example, 9999995 should return 995.
Or 99991001 should return 1001.

Please advise and thanks in advance!

Edited by - astralis on 02/27/2014 20:53:29

khtan
In (Som, Ni, Yak)

Singapore
17587 Posts

Posted - 02/27/2014 :  20:54:17  Show Profile  Reply with Quote
how about 1001, 991 etc ? what do you want for result ?

and what is the data type of that column ?


KH
Time is always against us


Edited by - khtan on 02/27/2014 20:54:58
Go to Top of Page

astralis
Yak Posting Veteran

USA
59 Posts

Posted - 02/27/2014 :  21:09:46  Show Profile  Reply with Quote
For the rows that don't have 9999 in front of them, I don't want them in my recordset, so ignored.

The data type is int.
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17587 Posts

Posted - 02/27/2014 :  21:16:42  Show Profile  Reply with Quote
assuming your column data type is string type

select stuff(col, 1, 4, '')
from   yourtable
where  col like '9999%'



KH
Time is always against us

Go to Top of Page

astralis
Yak Posting Veteran

USA
59 Posts

Posted - 02/27/2014 :  21:36:01  Show Profile  Reply with Quote
The datatype is int.
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17587 Posts

Posted - 02/27/2014 :  21:55:17  Show Profile  Reply with Quote
the query will still work


KH
Time is always against us

Go to Top of Page

astralis
Yak Posting Veteran

USA
59 Posts

Posted - 02/27/2014 :  21:58:45  Show Profile  Reply with Quote
Fantastic! Thank you!
Go to Top of Page

astralis
Yak Posting Veteran

USA
59 Posts

Posted - 02/27/2014 :  22:17:12  Show Profile  Reply with Quote
How could I use stuff(col,1,4,'') as an alias in an inner join?

This is my example, which doesn't work because aliases cannot be used in an inner join:

SELECT top 10 i.uniqueID,i.subject,i.image,i.brief,i.ctaction,i.ptype,
i.submittext,i.ctactionurl,i.idcampaign, i.live,
x.items_id as itemsID,
stuff(x.items_id, 1, 4, '') as itemstrimmed
FROM (item_petitions AS i INNER JOIN Items_x_Tags AS x ON i.idcampaign=itemstrimmed)
INNER JOIN item_tags AS t ON x.tag_id=t.tag_id
WHERE t.tag_name IN ('team')
AND t.items_id LIKE '9999%'
AND i.active = 1
ORDER BY i.idcampaign DESC;

Edited by - astralis on 02/27/2014 22:30:48
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17587 Posts

Posted - 02/27/2014 :  22:53:55  Show Profile  Reply with Quote
you can't use the column alias like that you have to specify the full expressson stuff(col, 1, 4, '') or you wrapped it in derived table or CTE


KH
Time is always against us


Edited by - khtan on 02/27/2014 22:54:23
Go to Top of Page

astralis
Yak Posting Veteran

USA
59 Posts

Posted - 02/28/2014 :  00:08:11  Show Profile  Reply with Quote
For performance, which method do you recommend?
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17587 Posts

Posted - 02/28/2014 :  00:38:45  Show Profile  Reply with Quote
no diff. The performance killer is the "col like '9999%'". It is implicitly convert to string and then compare with the 9999 string.

if there is a known possible max number, you can try

where col between 99990   and 99999
or    col between 999900  and 999999
or    col between 9999000 and 9999999
......



KH
Time is always against us

Go to Top of Page

astralis
Yak Posting Veteran

USA
59 Posts

Posted - 02/28/2014 :  00:52:57  Show Profile  Reply with Quote
ah, yes. What about col > 9999000? Better than between?
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17587 Posts

Posted - 02/28/2014 :  01:17:06  Show Profile  Reply with Quote
just add to the query accordingly


KH
Time is always against us

Go to Top of Page

sqlsaga
Yak Posting Veteran

USA
93 Posts

Posted - 02/28/2014 :  11:54:44  Show Profile  Reply with Quote
Hi astralis, Try this..

DECLARE @Input TABLE
(
ID INT
)

INSERT INTO @Input VALUES(99990001), (99991111),(9911111)

;WITH CTE AS
(
SELECT CASE WHEN LEFT(ID, 4) = 9999 THEN CONVERT(INT, SUBSTRING(CONVERT(VARCHAR(100),ID), 5, LEN(ID))) END AS Value
FROM @Input
)
SELECT * FROM CTE
WHERE Value IS NOT NULL


This should work fine for you.. Let us know...

Visit www.sqlsaga.com for more t-sql snippets and BI related how to's.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000