March 18, 2014 at 12:32 pm
How do you take a string and search for the first "-" and then drop everything from the start to, and including, the "-"?
March 18, 2014 at 12:40 pm
Hard coding where the '-' does not work since it varies, so the below does not split correctly
--RIGHT(col1,(LEN(col1)-6)) as col1,
I tried to fix it by using the below but it errors out with "Incorrect syntax near the keyword 'as'."
RIGHT(col1,LEN(col1)- CHARINDEX('-',col1) as col1,
March 18, 2014 at 12:49 pm
dwilliscp (3/18/2014)
Hard coding where the '-' does not work since it varies, so the below does not split correctly--RIGHT(col1,(LEN(col1)-6)) as col1,
I tried to fix it by using the below but it errors out with "Incorrect syntax near the keyword 'as'."
RIGHT(col1,LEN(col1)- CHARINDEX('-',col1) as col1,
The syntax error is because you didn't close the RIGHT function.
Here are a couple ways you could do this.
declare @String varchar(25) = 'asdf-qwer'
select SUBSTRING(@String, 0, charindex('-', @String, 0) + 1)
select LEFT(@String, charindex('-', @String, 0))
_______________________________________________________________
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/
March 18, 2014 at 12:51 pm
Or looking at your attempt maybe you want everything after the first occurrence of '-'?
If that is the case your code would work if you close the function.
select RIGHT(@String,LEN(@String)- CHARINDEX('-',@String)) as col1
_______________________________________________________________
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/
March 18, 2014 at 1:03 pm
Thanks... I dropped the RIGHT and used substring, and this seems to work
substring(col1,charindex('-',col1)+ 1,len(col1)) as col1,
March 19, 2014 at 5:55 pm
Just STUFF it:
declare @String varchar(25) = 'asdf-qwer';
SELECT STUFF(@String, 1+charindex('-', @String), 999, '');
🙂
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply