|
|||||||||
|
|||||||||
|
|||||||||
 |
|||||||||
| |
||
|
|
|
|
|||||||||
|
|||||||||
|
|||||||||
|
|||||||||
| |
||
|
|
|
| |||||||||
 |
|
|
«
Previous Thread
|
Next Thread
»
|
Thread Tools | Search this Thread | Rate Thread | Display Modes |
ASP Free Forums Sponsor:
|
|
|
|
#1
April 1st, 2005, 11:43 AM
|
|||
|
|||
|
String Manipulation in Query Result
I have the following rows in a table, i have to write a query which returns
results as shown Table Values Result -------------- ---------------- 100 --> 100 100N --> 100N 100NCA ---> 100N(CA) 200J ---> 2000J 2000CA ---> 2000(CA) Logic: Check last two characters of string, if both are alphabets, then add ( ) before and after and return...for anything else just return original How can i write such a query? Thanks in advance
|
|
#2
April 2nd, 2005, 06:04 AM
|
|||
|
|||
|
Try this, I tested it with your inputs and it seemed ok
eg SELECT dbo.fnCheckLastTwoChars('1000j')... Code:
CREATE FUNCTION fnCheckLastTwoChars
(
@myString varchar(20)
)
RETURNS varchar(20)
AS
BEGIN
--declare variables
DECLARE @my2ndRight char(1), @my1stRight char(1),@myLeft varchar(20)
DECLARE @myLength smallint, @myNewString varchar(20)
--store the different parts of the string
SET @myLength = (LEN(@myString) - 1)
SET @my2ndRight = RIGHT(@myString,1)
SET @my1stRight = SUBSTRING(@myString,(@myLength),1)
SET @myLeft = LEFT(@myString,@myLength - 1)
IF IsNumeric(@my1stRight) = 1
BEGIN
SET @myNewString = @myString
END
ELSE
BEGIN
IF IsNumeric(@my2ndRight) = 0
BEGIN
SET @myNewString = (@myLeft + '(' + @my1stRight + @my2ndRight + ')')
END
ELSE
BEGIN
SET @myNewString = @myString
END
END
RETURN @myNewString
END
|
|
| Viewing: ASP Free Forums > Database > Microsoft SQL Server > String Manipulation in Query Result |
| Thread Tools | Search this Thread |
| Display Modes | Rate This Thread |
|
|
 |
|
|
|
|
|
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
Linear Mode
