Help with CHARINDEX requested - SQL Server Forums

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

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Help with CHARINDEX requested

Author

Topic

nietzky
Yak Posting Veteran

75 Posts

Posted - 2013-09-12 : 10:24:38

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

10 Posts

Posted - 2013-09-12 : 10:39:54

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 #*

djj55
Constraint Violating Yak Guru

352 Posts

Posted - 2013-09-12 : 10:42:10

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

Cooper-5
Starting Member

10 Posts

Posted - 2013-09-12 : 10:57:23

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 #*

nietzky
Yak Posting Veteran

75 Posts

Posted - 2013-09-12 : 11:01:28

Thank you Cooper-5. By any chance can you show me how to do this? That is exactly the challenge is was facing before.

Cooper-5
Starting Member

10 Posts

Posted - 2013-09-12 : 11:26:45

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 #*

Cooper-5
Starting Member

10 Posts

Posted - 2013-09-12 : 11:42:29

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 #*

nietzky
Yak Posting Veteran

75 Posts

Posted - 2013-09-12 : 13:01:46

Thank you Cooper-5. I am good now!

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2013-09-12 : 16:17:56

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

VasiAnu
Starting Member

9 Posts

Posted - 2013-09-13 : 01:40:03

It would Probably work

SELECT REVERSE(SUBSTRING(REVERSE(OrderNo),1,PATINDEX('%[A-Z OR #]%',REVERSE(OrderNo))-1)) from [your_table_Name]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources