Character arrays in T-SQL
-
Hi all,
Can someone help me do we have character arrays in T-SQL in SQLServer2000? If so could you please give the syntax? I want to have a character array which takes values as a string and we can do operations on individual characters of the string........for eg varchar(5)='WORDS'
I need to need to do some manipulations which requires the w , o , r , d , s individually..........
Thanks
Vamsi
-
I'm not sure what you need, but you could try to read following topics in BOL:
SUBSTRING, CHARINDEX, PATINDEX, REPLACE ... and possibly some other string functions. These functions allow you to find what character is on certain position, or find out on which position is certain character, and manipulate it.
If you will still need assistance, please post more details about your problem.
Example - insert Q on second position, in the other part replace all occurences of 'd' with 'Y':
declare @test-2 varchar(5)
set @test-2 = 'words'
select SUBSTRING(@test,1,1)+'Q'+ REPLACE(SUBSTRING(@test,2,4),'d','Y')
result : wQorYs
-
No Arrays <period>. An array is a repeating group, not unlike like rows in a table. So it's kind of 'not allowed' in a relational database.
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
-
It seems to me you think backwards from parsing a string. In other words, if I have an SP with a string passed in delimited by | [ pipe ], I can take each of those strings/characters and do work with them.
This looks to be requesting the opposite. Get a full word and break up each character to do work on them... Not quite an array, per se, but similar.
Is this the question?
I wasn't born stupid - I had to study.
-
Vamsi,
Although there's nothing specific about using character arrays to process the W, o, r, d, s, could you be a bit more specific about what you are trying to do? There's some amazing stuff you can do in SQL and character array emulation is certainly one of them.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Hear, hear!
Here's a simple example. If you give us the 'bigger picture', we'll be able to show you the best tricks to achieve what you need.
--Inputs
declare @x varchar(100)
set @x = 'WORDS'
--Table of letters (like an array)
select substring(@x, number, 1) as v
from master.dbo.spt_values where number between 1 and len(@x) group by number order by number
--Example manipulation
declare @y varchar(100)
select @y = isnull(@y, '') + char(ascii(substring(@x, number, 1))+1)
from master.dbo.spt_values where number between 1 and len(@x) group by number order by number
select @x as 'Original', @y as 'Manipulated (shifted by 1)'
/*Results
v
----
W
O
R
D
S
Original Manipulated (shifted by 1)
---------------- -----------------------------
WORDS XPSET
*/
Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.
-
Nice job, Ryan... I was being lazy... not enough coffee that day...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
But your 'being lazy', Jeff, is most people's 'working my arse off'
Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.
-
That must be why I'm "a little behind"
(Sorry, couldn't help myself 
)--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply