July 25, 2011 at 10:31 am
i inherited a stored procedure and have to make a few modifications. as i am not too familiar with SUBSTRING functions i need some help. i am fine with the first part, as i still need to remove the first character. the second portion of the script works with a field that used to have a single character and an extra set of double quotes. the extra double quotes were being removed so it left the single character. now i have 4 total characters in this field (including the double quotes) and need to remove the double quotes, leaving three characters. how do i adjust the second part of this script to accomodate this requirement?
UPDATE import_table SET cost_code=RTRIM(SUBSTRING(cost_code,2,DATALENGTH(cost_code)-1)), field121=RTRIM(SUBSTRING(field121,1,DATALENGTH(field121)-1))
thanks!
JS
July 25, 2011 at 10:48 am
DECLARE @Var VARCHAR(10)
SET @Var = '123'''''
SELECT @Var, REPLACE(@Var, '''''', '''')
July 25, 2011 at 10:59 am
You could always use the RIGHT and LEFT functions.
Declare @MyVar char(6);
Set @MyVar = 'abcdef';
Select @MyVar = LEFT(RIGHT(@MyVar,5),4);
Select @MyVar;
UPDATE import_table
SET cost_code=@MyVar;
July 25, 2011 at 12:34 pm
would this approach require that the field containing the data to be truncated is all the same value? in the below sample, i just need the double quotes removed.
field1, field2, field3
Frank, White, USA"
Bob, Smith, USA"
Manuel, Santiago, ESP"
July 25, 2011 at 12:36 pm
Where the 5 and 4 are in my code, these are the string lengths. If you want a variable strength length, then you just replace the numbers with LEN(Myfield)-1 and LEN(Myfield)-2 respectively. LEN() is the 'how long is this string' function.
Does that make sense?
EDIT: Actually, I take this partially back. The LEN(Myfield)-2 might not work correctly. You'll have to play with it, maybe update the Variable twice or extend it to be LEN(Left(Myfield,LEN(Myfield)-1))-1 or something like that. Play with it. See how it works.
July 25, 2011 at 12:52 pm
yep gotcha on the string length parts. does the value that i set for the variable matter?
Set @MyVar = 'abcdef';
July 26, 2011 at 7:33 am
justintime (7/25/2011)
yep gotcha on the string length parts. does the value that i set for the variable matter?
We're just using the variable as an example. you don't have to set or create a variable in your code. Just use the Right() and Left() or Replace() function as we gave you and then put the column name in the place where the variable currently sits.
July 26, 2011 at 1:56 pm
is there anything wrong with using the below statement? it seems to give me the results i'm looking for.
SELECT SUBSTRING(field3, 1, 3) AS 'trimmed last field' from dbo.test1
July 26, 2011 at 2:20 pm
Nothing wrong with it as long you realize you are only going to the first 3 characters in your string.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 27, 2011 at 5:25 am
Sean Lange (7/26/2011)
Nothing wrong with it as long you realize you are only going to the first 3 characters in your string.
In addition to this statement, if the datatype is varchar() and the actual length of a given field is only 2 characters, you won't even get the first 3 characters in the string. Just the 2 that are in the string. And a length of 5 will terminate at 3.
So:
ab will be pulled as ab
abc will be pulled as abc
abcde will be pulled as abc.
July 27, 2011 at 6:03 am
thanks to all for your help. i have a second question but let me know if i should start a new topic. how would you extract part of a string, based on a specific character? i need to extract the prefix of an email address so that the given 'justin@email.com' returns 'justin'? the email lengths are variable so i need to trim based on the '@'.
P.S. i will also need to extract the domain portion so i will also need the 'email.com' part.
thanks in advance.
justin
July 27, 2011 at 6:15 am
Look into the Patindex and Charindex functions (you can find them in Books Online by searching for those keywords). You can use those to determine where the @ is, then use that in your Substring() as the start-1 or the end-1.
July 27, 2011 at 6:20 am
The ridiculously exotic version.
SELECT REPLACE(PARSENAME(REPLACE(REPLACE('justin@email.com', '.', '%'), '@', '.'), 2), '%', '.')
SELECT REPLACE(PARSENAME(REPLACE(REPLACE('justin@email.com', '.', '%'), '@', '.'), 1), '%', '.')
The "real" version is to use substring + charindex
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply