Remove the first four characters

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

General SQL Server Forums

New to SQL Server Programming

Remove the first four characters

Author

Topic

ConradK
Posting Yak Master

140 Posts

Posted - 2009-12-03 : 10:15:28

my dataset is soemthing like:

aim-12234
nsc-jdk23
yuk-aim23

etc..

I need to remove the first 4 characters. I could just run a replace on every prefix we have, but there is something like 200 of them, and that seems a little long winded and inefficient. I tried to google the Trim function, but all I could find is it removing white spaces, and people asking it to do the same thing as REPLACE(my_data, 'info', ''), but I don't want a string of replaces...

help?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-12-03 : 10:19:06

select col, substring(col,5,len(col)) as new_col from your_table

Madhivanan

Failing to plan is Planning to fail

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-12-03 : 10:19:08

Easy.


DECLARE @foo VARCHAR(255)
SELECT @foo = 'van-asdlkajlfjksf'

SELECT RIGHT(@foo, LEN(@foo) - 4)

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-12-03 : 10:19:58

try something like this:
set column = replace(column,left(column,4),'')

No, you're never too old to Yak'n'Roll if you're too young to die.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-12-03 : 10:20:37

Lol madhivanan. 2 seconds eh?

Anyway -- both will work but both will fail if there is any data there with a length of less than 5 characters so be aware of that.

good luck

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-12-03 : 10:20:58

Cool!
3 answers - 3 ways!

No, you're never too old to Yak'n'Roll if you're too young to die.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-12-03 : 10:22:14

quote:
Originally posted by webfred

try something like this:
set column = replace(column,left(column,4),'')

No, you're never too old to Yak'n'Roll if you're too young to die.

Beware of this

Madhivanan

Failing to plan is Planning to fail

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-12-03 : 10:23:12

Hi Webfred,

Your method might be a little broken depending on the data.

Consider this:


DECLARE @foo VARCHAR(255)
SELECT @foo = 'van-asdlvan-kajlfjksf'

SELECT RIGHT(@foo, LEN(@foo) - 4)

SELECT replace(@foo,left(@foo,4),'')

The two selects give different answers.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-12-03 : 10:23:35

quote:
Originally posted by madhivanan

quote:
Originally posted by webfred

try something like this:
set column = replace(column,left(column,4),'')

No, you're never too old to Yak'n'Roll if you're too young to die.

Beware of this

Madhivanan

Failing to plan is Planning to fail

why

No, you're never too old to Yak'n'Roll if you're too young to die.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-12-03 : 10:24:45

quote:
Originally posted by webfred

quote:
Originally posted by madhivanan

quote:
Originally posted by webfred

try something like this:
set column = replace(column,left(column,4),'')

No, you're never too old to Yak'n'Roll if you're too young to die.

Beware of this

Madhivanan

Failing to plan is Planning to fail

why

No, you're never too old to Yak'n'Roll if you're too young to die.

See my post above.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-12-03 : 10:24:47

quote:
Originally posted by webfred

quote:
Originally posted by madhivanan

quote:
Originally posted by webfred

try something like this:
set column = replace(column,left(column,4),'')

No, you're never too old to Yak'n'Roll if you're too young to die.

Beware of this

Madhivanan

Failing to plan is Planning to fail

why

No, you're never too old to Yak'n'Roll if you're too young to die.

See the previous answer

Madhivanan

Failing to plan is Planning to fail

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-12-03 : 10:25:02

yep!
My bad.

No, you're never too old to Yak'n'Roll if you're too young to die.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-12-03 : 10:26:19

So I was slower AND less correct - shit happens...

No, you're never too old to Yak'n'Roll if you're too young to die.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-12-03 : 10:26:38

madhivanan: HA -- got you back this time, 2 seconds to me. A tie!

cheers guys. Doubt OP thought he'd start a thread like this!

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-12-03 : 10:36:08

There's only one way to rock but there are many ways to solve this problem.

DECLARE @foo VARCHAR(255)
SELECT @foo = 'van-asdlvan-kajlfjksf'

SELECT RIGHT(@foo, LEN(@foo) - 4)

select stuff(@foo,1,4,'') as webfred

No, you're never too old to Yak'n'Roll if you're too young to die.

ConradK
Posting Yak Master

140 Posts

Posted - 2009-12-03 : 11:01:07

wow, what did i unleash?

okay, so my code is under 'SELECT', and most of it looks like

, pe.whatever as "The stuff"
, pm.blargh as "other stuff"
, REPLACE(p.productname, 'bad things', 'good things') as "stuff name"

and I was hoping for something that looks like that? I can do other things, but do not understand what a DECLARE is, nor what this @foo stuff is....

If i am udnerstanding correct, if i put the DECLARE statment above my selct statement, and then go

, RIGHT(pe.productcode, LEN(pe.productcode) -4) and that will do it? I'm a little lost...

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-12-03 : 11:01:30

Each way seems to be equally fast (or equally slow if you a glass half empty kinda guy).

Test suit:


IF OBJECT_ID('tempDb..#testLoad') IS NOT NULL DROP TABLE #testLoad

CREATE TABLE #testLoad (
	[ID] INT IDENTITY(1,1) PRIMARY KEY
	, [textVal] VARCHAR(64)
	)

-- Populate #testLoad with random strings
INSERT #testLoad ([textVal])
SELECT CAST(CAST(NEWID() AS VARBINARY(32)) AS VARCHAR(64))
FROM
	(SELECT 1 AS [a] UNION SELECT 2 AS [b] UNION SELECT 3 AS [c]) a
	CROSS JOIN (SELECT 1 AS [a] UNION SELECT 2 AS [b] UNION SELECT 3 AS [c]) b
	CROSS JOIN (SELECT 1 AS [a] UNION SELECT 2 AS [b] UNION SELECT 3 AS [c]) c
	CROSS JOIN (SELECT 1 AS [a] UNION SELECT 2 AS [b] UNION SELECT 3 AS [c]) d
	CROSS JOIN (SELECT 1 AS [a] UNION SELECT 2 AS [b] UNION SELECT 3 AS [c]) e
	CROSS JOIN (SELECT 1 AS [a] UNION SELECT 2 AS [b] UNION SELECT 3 AS [c]) f
	CROSS JOIN (SELECT 1 AS [a] UNION SELECT 2 AS [b] UNION SELECT 3 AS [c]) g
	CROSS JOIN (SELECT 1 AS [a] UNION SELECT 2 AS [b] UNION SELECT 3 AS [c]) h
	CROSS JOIN (SELECT 1 AS [a] UNION SELECT 2 AS [b] UNION SELECT 3 AS [c]) i
	CROSS JOIN (SELECT 1 AS [a] UNION SELECT 2 AS [b] UNION SELECT 3 AS [c]) j
	CROSS JOIN (SELECT 1 AS [a] UNION SELECT 2 AS [b] UNION SELECT 3 AS [c]) k
	CROSS JOIN (SELECT 1 AS [a] UNION SELECT 2 AS [b] UNION SELECT 3 AS [c]) l
GO

SET STATISTICS IO ON
SET STATISTICS TIME ON

-- Madhivanan
SELECT SUBSTRING([textVal], 5, LEN([textVal])) AS [Madhivanan] FROM #testLoad 

-- Charlie
SELECT RIGHT([textVal], LEN([textVal]) -4) AS [Charlie] FROM #testLoad 

-- Webfred
SELECT STUFF([textVal],1,4,'') AS webfred FROM #testLoad

Results over 4 runs


Madhivanan
1 CPU time = 281 ms,  elapsed time = 3619 ms.
2 CPU time = 297 ms,  elapsed time = 3727 ms.
3 CPU time = 140 ms,  elapsed time = 3649 ms.
4 CPU time = 328 ms,  elapsed time = 3809 ms.

Charlie
1 CPU time = 281 ms,  elapsed time = 3625 ms.
2 CPU time = 188 ms,  elapsed time = 3759 ms.
3 CPU time = 329 ms,  elapsed time = 3753 ms.
4 CPU time = 313 ms,  elapsed time = 3836 ms.

Webfred
1 CPU time = 266 ms,  elapsed time = 3869 ms.
2 CPU time = 250 ms,  elapsed time = 3765 ms.
3 CPU time = 296 ms,  elapsed time = 3721 ms.
4 CPU time = 250 ms,  elapsed time = 3673 ms.

NB: multiple edits due to my complete lack of typing skills

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-12-03 : 11:06:35

quote:
Originally posted by ConradK

wow, what did i unleash?

okay, so my code is under 'SELECT', and most of it looks like

, pe.whatever as "The stuff"
, pm.blargh as "other stuff"
, REPLACE(p.productname, 'bad things', 'good things') as "stuff name"

and I was hoping for something that looks like that? I can do other things, but do not understand what a DECLARE is, nor what this @foo stuff is....

If i am udnerstanding correct, if i put the DECLARE statment above my selct statement, and then go

, RIGHT(pe.productcode, LEN(pe.productcode) -4) and that will do it? I'm a little lost...

Hi Conrad.

Don't worry about the DECLARE stuff -- I was just showing using a variable rather than a table to get a quick example going.

Just pick one of the options (they are all pretty much equal) -- take madhivanan's as it's all-ready in a nice form'


SELECT
    pe.whatever as "The stuff"
    , pm.blargh as "other stuff"
    , REPLACE(p.productname, 'bad things', 'good things') as "stuff name"
    , SUBSTRING([my_data], 5, LEN([my_data])) AS [truncated_my_data]
FROM
    <<THE TABLE>>

Post the table name and column name if that's still not 100% clear

Sorry about the discussion -- we all came up with a different way to address the issue.

Regards,

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

ConradK
Posting Yak Master

140 Posts

Posted - 2009-12-03 : 11:12:44

cool.

I think i get this:

SUBSTRING([my_data], 5, LEN([my_data])) AS [truncated_my_data]

That seems to make sense to me. I'm guessing SUBSTRING limits the number of character by a value, and you are replacing 5 with.. wait, what? I can understand how to plug and play that data. In my code it will be

SUBSTRING(pe.productcode, 5, LEN(pe.productcode)) AS "model number"

and that makes sense as a validated function structure to me... but what is it doing?

the values for pe.productcode are like

aim-234jjk
nsc-jkaldjlk3
bla-hi-ajkd

etc. ad nausium(sp).

Point is, just need to trim the first 4 and have the rest be whatever neumber of character and however it is left over...

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-12-03 : 11:20:33

From the documentation on Subrstring:

---------------------------
Returns part of a character, binary, text, or image expression.

Syntax
SUBSTRING ( expression ,start , length )

----------------------------

So for this example using substring the function (substring) takes the string (expression) which is the value of each row for your column. It returns part of the string (starting at character number (start) and counts from there a number of characters (length) after that.

So for the string 'yuk-aim23'
and for the command SUBSTRING('yuk-aim23', 5, LEN('yuk-aim23')

This is what happens:

Substring finds the 5th character (a)
And returns a number of characters from this position = len('yuk-aim23') which is 9) -- as this is longer than the number of characters left in the string it returns all of them starting at position 5 (a).

Hope this helps.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-12-03 : 11:25:28

You can run this in management studio and play around to get a feel for the function. It uses a variable so no data will be changed.


-- This is a variable, it holds 1 value at a time
DECLARE @textValue VARCHAR(50)

-- Assign @textValue the string 'I hate Mondays!'
SET @textValue = 'I hate Mondays!'

-- Trim the first 4 characters ('I ha')
SELECT SUBSTRING(@textValue, 5, LEN(@textValue))

-- Get the string starting at position 5 to position 10
SELECT SUBSTRING(@textValue, 5, 5)

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources