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)
 Remove first four characters

Author  Topic 

astralis
Yak Posting Veteran

62 Posts

Posted - 2014-02-27 : 20:51:54
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!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2014-02-27 : 20:54:17
how about 1001, 991 etc ? what do you want for result ?

and what is the data type of that column ?


KH
[spoiler]Time is always against us[/spoiler]

Go to Top of Page

astralis
Yak Posting Veteran

62 Posts

Posted - 2014-02-27 : 21:09:46
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)

17689 Posts

Posted - 2014-02-27 : 21:16:42
assuming your column data type is string type

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



KH
[spoiler]Time is always against us[/spoiler]

Go to Top of Page

astralis
Yak Posting Veteran

62 Posts

Posted - 2014-02-27 : 21:36:01
The datatype is int.
Go to Top of Page

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2014-02-27 : 21:55:17
the query will still work


KH
[spoiler]Time is always against us[/spoiler]

Go to Top of Page

astralis
Yak Posting Veteran

62 Posts

Posted - 2014-02-27 : 21:58:45
Fantastic! Thank you!
Go to Top of Page

astralis
Yak Posting Veteran

62 Posts

Posted - 2014-02-27 : 22:17:12
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;
Go to Top of Page

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2014-02-27 : 22:53:55
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
[spoiler]Time is always against us[/spoiler]

Go to Top of Page

astralis
Yak Posting Veteran

62 Posts

Posted - 2014-02-28 : 00:08:11
For performance, which method do you recommend?
Go to Top of Page

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2014-02-28 : 00:38:45
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
[spoiler]Time is always against us[/spoiler]

Go to Top of Page

astralis
Yak Posting Veteran

62 Posts

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

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2014-02-28 : 01:17:06
just add to the query accordingly


KH
[spoiler]Time is always against us[/spoiler]

Go to Top of Page

sqlsaga
Yak Posting Veteran

93 Posts

Posted - 2014-02-28 : 11:54:44
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
   

- Advertisement -