SQL Server Forums
Profile | Register | 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
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

nietzky
Yak Posting Veteran

72 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
308 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

72 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

72 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
Constraint Violating Yak Guru

USA
340 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  
 New 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