Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Help with CHARINDEX requested
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

nietzky
Yak Posting Veteran

75 Posts

Posted - 09/12/2013 :  10:24:38  Show Profile  Reply with Quote
I have a column ORDERNO that contains values that are showing below.
I would like to format/clean up these values so they show only numeric values after the # character. I know CHARINDEX could be used with SUBSTRING or REPLACE but having problem writing the actual select statement. Can you please help out?

ORDERNO

1;#BQ28960
26;#BQ31400
25;#BQ31091
22;#BQ29048
22;#BQ29048
21;#BQ31624
16;#BQ34309
24;#BQ28698
23;#31093
3;#61008

expected output:

28960
31400
31091
29048
29048
31624
34309
28698
31093
61008


Cooper-5
Starting Member

United Kingdom
10 Posts

Posted - 09/12/2013 :  10:39:54  Show Profile  Reply with Quote
probably a better way to do this but try this.


select right ('26;#BQ31400', len('26;#BQ31400') -(CHARINDEX('#','26;#BQ31400')+2))

hope it helps :)

*# Nothing is truly idiot proof, because the world keeps producing a higher class of idiot #*
Go to Top of Page

djj55
Constraint Violating Yak Guru

USA
352 Posts

Posted - 09/12/2013 :  10:42:10  Show Profile  Reply with Quote
I would suggest writing a user defined function because you will need to loop through the characters. If you will only have BQ then you could use the REPLACE command in a select statement.

djj
Go to Top of Page

Cooper-5
Starting Member

United Kingdom
10 Posts

Posted - 09/12/2013 :  10:57:23  Show Profile  Reply with Quote
im personally against UDF's but im biased haha just a small issue with my answer ive added 2 to the charindex result to accomodate for the 'BQ' an just realised that this is not constant.

you made need do a second charindex to pick the characters and then do a right to catch the last 5 digits.

...just in hindsight if that number is always 5 digits just do a right 5 on the select

*# Nothing is truly idiot proof, because the world keeps producing a higher class of idiot #*
Go to Top of Page

nietzky
Yak Posting Veteran

75 Posts

Posted - 09/12/2013 :  11:01:28  Show Profile  Reply with Quote
Thank you Cooper-5. By any chance can you show me how to do this? That is exactly the challenge is was facing before.
Go to Top of Page

Cooper-5
Starting Member

United Kingdom
10 Posts

Posted - 09/12/2013 :  11:26:45  Show Profile  Reply with Quote
select case when left(right ('26;#BQ31400', len('26;#BQ31400') -(CHARINDEX('#','26;#BQ31400'))),2) = 'BQ' then
right ('26;#BQ31400', len('26;#BQ31400') -(CHARINDEX('#','26;#BQ31400')+2))
else
right ('26;#BQ31400', len('26;#BQ31400') -(CHARINDEX('#','26;#BQ31400')))
end


really long winded but try this one out

*# Nothing is truly idiot proof, because the world keeps producing a higher class of idiot #*
Go to Top of Page

Cooper-5
Starting Member

United Kingdom
10 Posts

Posted - 09/12/2013 :  11:42:29  Show Profile  Reply with Quote
okay slightly tidier version of this one which may be more useful as it does not require the left function.

declare @val varchar(20)
set @val = '26;#BQ31400'

select case when isnumeric(right (@val, len(@val) -(CHARINDEX('#',@val)))) = 0 then
right (@val, len(@val) -(CHARINDEX('#',@val)+2))
else
right (@val, len(@val) -(CHARINDEX('#',@val)))
end

*# Nothing is truly idiot proof, because the world keeps producing a higher class of idiot #*
Go to Top of Page

nietzky
Yak Posting Veteran

75 Posts

Posted - 09/12/2013 :  13:01:46  Show Profile  Reply with Quote
Thank you Cooper-5. I am good now!
Go to Top of Page

ScottPletcher
Aged Yak Warrior

USA
550 Posts

Posted - 09/12/2013 :  16:17:56  Show Profile  Reply with Quote
I think this is simpler and more accurate:
RIGHT(ORDERNO, PATINDEX('%[^0-9]%', REVERSE(ORDERNO)) - 1)

For example:


SELECT
    ORDERNO,
    RIGHT(ORDERNO, PATINDEX('%[^0-9]%', REVERSE(ORDERNO)) - 1) AS second_method,
    case when isnumeric(right (ORDERNO, len(ORDERNO) -(CHARINDEX('#',ORDERNO)))) = 0 then
	right (ORDERNO, len(ORDERNO) -(CHARINDEX('#',ORDERNO)+2))
else
	right (ORDERNO, len(ORDERNO) -(CHARINDEX('#',ORDERNO)))
end AS first_method

FROM (
          SELECT '1;#BQ28960' AS ORDERNO
UNION ALL SELECT '26;#BQ31400'
UNION ALL SELECT '25;#BQ31091'
UNION ALL SELECT '22;#BQ29048'
UNION ALL SELECT '22;#BQ29048'
UNION ALL SELECT '21;#BQ31624'
UNION ALL SELECT '16;#BQ34309'
UNION ALL SELECT '24;#BQ28698'
UNION ALL SELECT '23;#31093'
UNION ALL SELECT '3;#61008'
UNION ALL SELECT '2223wws24;#abcbqr23321'
) AS test_data


Go to Top of Page

VasiAnu
Starting Member

India
9 Posts

Posted - 09/13/2013 :  01:40:03  Show Profile  Reply with Quote
It would Probably work

SELECT REVERSE(SUBSTRING(REVERSE(OrderNo),1,PATINDEX('%[A-Z OR #]%',REVERSE(OrderNo))-1)) from [your_table_Name]
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000