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

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]

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.

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]

astralis
Yak Posting Veteran

62 Posts

Posted - 2014-02-27 : 21:36:01

The datatype is int.

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]

astralis
Yak Posting Veteran

62 Posts

Posted - 2014-02-27 : 21:58:45

Fantastic! Thank you!

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;

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]

astralis
Yak Posting Veteran

62 Posts

Posted - 2014-02-28 : 00:08:11

For performance, which method do you recommend?

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]

astralis
Yak Posting Veteran

62 Posts

Posted - 2014-02-28 : 00:52:57

ah, yes. What about col > 9999000? Better than between?

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]

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.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources